Guest User

Untitled

a guest
Jul 4th, 2017
27
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.83 KB | None | 0 0
  1. LIBNAME OPSUPL "E:\SASDEV\Data\Upload\Operation\Src_Upload";
  2. LIBNAME OPSFINAL "E:\SASDEV\Operation\Data\Final_Source";
  3. Libname Source ODBC Datasrc = SAS_SOURCE User = SAS_SOURCE PASSWORD= "{SAS002}12F6080547C51D2A378A8D3647BEF262";
  4. Libname Oraodbc ODBC Datasrc = Oracle_Odbc User = sasprod PASSWORD= "{SAS002}0A80F20B1CEDC1FE48CEA8413789374541EA38FD";
  5. libname ANAFIN "E:\Testing\Portfolio data";
  6. LIBNAME ANAUPL "E:\SASDEV\Data\Upload\Analytics\Src_Upload";
  7. LIBNAME GEO 'Z:\Training\Avinash Barnwal\Geo\Sept15';
  8. LIBNAME UATUPL "Z:\SASDEV\Data\Upload\Analytics\Src_Upload";
  9. LIBNAME UATFIN "Z:\SASDEV\Analytics\Data\Final_Source";
  10. LIBNAME UOPSUPL "Z:\SASDEV\Data\Upload\Operation\Src_Upload";
  11. LIBNAME UOPSFIN "Z:\SASDEV\Operation\Data\Final_Source";
  12.  
  13. option compress=yes;
  14.  
  15. /*Upload shareholder grouping master*/
  16.  
  17. data WORK.shareholder_group_name ;
  18. %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
  19. infile 'G:\Ravi\Working Files\shareholder_group_name.csv' delimiter = ',' MISSOVER DSD
  20. lrecl=32767 firstobs=2 ;
  21. informat Shareholder $60. ;
  22. informat Fund $60. ;
  23. format Shareholder $60. ;
  24. format Fund $60. ;
  25. input
  26. Shareholder $
  27. Fund $
  28. ;
  29. if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
  30. run;
  31.  
  32. data uatupl.shareholder_group_name;
  33. set shareholder_group_name;
  34. run;
  35.  
  36. /*Upload collated file. Comment the code after uploading*/
  37. data WORK.SHAREHOLDING_POSITION ;
  38. %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
  39. infile 'G:\Ravi\Working Files\Shareholding_Position.csv' delimiter = ',' MISSOVER DSD
  40. lrecl=32767 firstobs=2 ;
  41. informat HOLDER $50. ;
  42. informat HOLD_MINOR $3. ;
  43. informat TYPE $3. ;
  44. informat TOTAL_SHAR best32. ;
  45. informat DATA_MONTH DATE9. ;
  46. format HOLDER $50. ;
  47. format HOLD_MINOR $3. ;
  48. format TYPE $3. ;
  49. format TOTAL_SHAR best12. ;
  50. format DATA_MONTH DATE9. ;
  51. input
  52. HOLDER $
  53. HOLD_MINOR $
  54. TYPE $
  55. TOTAL_SHAR
  56. DATA_MONTH
  57. ;
  58. if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
  59. run;
  60.  
  61. data uatupl.shareholding_position;
  62. set shareholding_position;
  63. run;
  64.  
  65. %let curr_dt = 30JUN2017;
  66.  
  67. data _null_;
  68. call symputx("week_dt",put(intnx('week',"&curr_dt."d,-1,'S'),date9.));
  69. call symputx("month_dt",put(intnx('week',"&curr_dt."d,-4,'S'),date9.));
  70. call symputx("qtr_dt",put(intnx('week',"&curr_dt."d,-12,'S'),date9.));
  71. call symputx("year_dt",put(intnx('week',"&curr_dt."d,-52,'S'),date9.));
  72.  
  73. run;
  74.  
  75. %put &curr_dt;
  76. %put &week_dt;
  77. %put &month_dt;
  78. %put &qtr_dt;
  79. %put &year_dt;
  80.  
  81. data curr_data;
  82. set anaupl.ben_pos;
  83. where data_month = "&curr_dt."d;
  84. run;
  85.  
  86. data week_data;
  87. set anaupl.ben_pos;
  88. where data_month = "&week_dt."d;
  89. run;
  90.  
  91. data month_data;
  92. set anaupl.ben_pos;
  93. where data_month = "&month_dt."d;
  94. run;
  95.  
  96. data qtr_data;
  97. set anaupl.ben_pos;
  98. where data_month = "&qtr_dt."d;
  99. run;
  100.  
  101. data year_data;
  102. set anaupl.ben_pos;
  103. where data_month = "&year_dt."d;
  104. run;
  105.  
  106. proc sql;
  107. Create table week_diff as
  108. Select T1.*, T2.holder as holder1, t2.total_shar as shares, t2.data_month as week_date
  109. from curr_data as T1 full join week_data as T2
  110. on T1.holder = T2.holder;
  111. quit;
  112.  
  113. data week_diff1;
  114. set week_diff;
  115. format difference 8.;
  116. if total_shar eq . and shares ne . then difference = shares;
  117. else if total_shar ne . and shares eq . then difference = total_shar;
  118. else if total_shar ne . and shares ne . then do;
  119. shares = shares*-1;
  120. difference = sum(total_shar, shares);
  121. end;
  122. run;
  123.  
  124. proc sort data=week_diff1 out=week_diff2;
  125. by descending difference;
  126. run;
  127.  
  128. HOLDER HOLD_MINOR TYPE TOTAL_SHARES DATA_MONTH
Add Comment
Please, Sign In to add comment