Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- LIBNAME OPSUPL "E:\SASDEV\Data\Upload\Operation\Src_Upload";
- LIBNAME OPSFINAL "E:\SASDEV\Operation\Data\Final_Source";
- Libname Source ODBC Datasrc = SAS_SOURCE User = SAS_SOURCE PASSWORD= "{SAS002}12F6080547C51D2A378A8D3647BEF262";
- Libname Oraodbc ODBC Datasrc = Oracle_Odbc User = sasprod PASSWORD= "{SAS002}0A80F20B1CEDC1FE48CEA8413789374541EA38FD";
- libname ANAFIN "E:\Testing\Portfolio data";
- LIBNAME ANAUPL "E:\SASDEV\Data\Upload\Analytics\Src_Upload";
- LIBNAME GEO 'Z:\Training\Avinash Barnwal\Geo\Sept15';
- LIBNAME UATUPL "Z:\SASDEV\Data\Upload\Analytics\Src_Upload";
- LIBNAME UATFIN "Z:\SASDEV\Analytics\Data\Final_Source";
- LIBNAME UOPSUPL "Z:\SASDEV\Data\Upload\Operation\Src_Upload";
- LIBNAME UOPSFIN "Z:\SASDEV\Operation\Data\Final_Source";
- option compress=yes;
- /*Upload shareholder grouping master*/
- data WORK.shareholder_group_name ;
- %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
- infile 'G:\Ravi\Working Files\shareholder_group_name.csv' delimiter = ',' MISSOVER DSD
- lrecl=32767 firstobs=2 ;
- informat Shareholder $60. ;
- informat Fund $60. ;
- format Shareholder $60. ;
- format Fund $60. ;
- input
- Shareholder $
- Fund $
- ;
- if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
- run;
- data uatupl.shareholder_group_name;
- set shareholder_group_name;
- run;
- /*Upload collated file. Comment the code after uploading*/
- data WORK.SHAREHOLDING_POSITION ;
- %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
- infile 'G:\Ravi\Working Files\Shareholding_Position.csv' delimiter = ',' MISSOVER DSD
- lrecl=32767 firstobs=2 ;
- informat HOLDER $50. ;
- informat HOLD_MINOR $3. ;
- informat TYPE $3. ;
- informat TOTAL_SHAR best32. ;
- informat DATA_MONTH DATE9. ;
- format HOLDER $50. ;
- format HOLD_MINOR $3. ;
- format TYPE $3. ;
- format TOTAL_SHAR best12. ;
- format DATA_MONTH DATE9. ;
- input
- HOLDER $
- HOLD_MINOR $
- TYPE $
- TOTAL_SHAR
- DATA_MONTH
- ;
- if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
- run;
- data uatupl.shareholding_position;
- set shareholding_position;
- run;
- %let curr_dt = 30JUN2017;
- data _null_;
- call symputx("week_dt",put(intnx('week',"&curr_dt."d,-1,'S'),date9.));
- call symputx("month_dt",put(intnx('week',"&curr_dt."d,-4,'S'),date9.));
- call symputx("qtr_dt",put(intnx('week',"&curr_dt."d,-12,'S'),date9.));
- call symputx("year_dt",put(intnx('week',"&curr_dt."d,-52,'S'),date9.));
- run;
- %put &curr_dt;
- %put &week_dt;
- %put &month_dt;
- %put &qtr_dt;
- %put &year_dt;
- data curr_data;
- set anaupl.ben_pos;
- where data_month = "&curr_dt."d;
- run;
- data week_data;
- set anaupl.ben_pos;
- where data_month = "&week_dt."d;
- run;
- data month_data;
- set anaupl.ben_pos;
- where data_month = "&month_dt."d;
- run;
- data qtr_data;
- set anaupl.ben_pos;
- where data_month = "&qtr_dt."d;
- run;
- data year_data;
- set anaupl.ben_pos;
- where data_month = "&year_dt."d;
- run;
- proc sql;
- Create table week_diff as
- Select T1.*, T2.holder as holder1, t2.total_shar as shares, t2.data_month as week_date
- from curr_data as T1 full join week_data as T2
- on T1.holder = T2.holder;
- quit;
- data week_diff1;
- set week_diff;
- format difference 8.;
- if total_shar eq . and shares ne . then difference = shares;
- else if total_shar ne . and shares eq . then difference = total_shar;
- else if total_shar ne . and shares ne . then do;
- shares = shares*-1;
- difference = sum(total_shar, shares);
- end;
- run;
- proc sort data=week_diff1 out=week_diff2;
- by descending difference;
- run;
- HOLDER HOLD_MINOR TYPE TOTAL_SHARES DATA_MONTH
Add Comment
Please, Sign In to add comment