Advertisement
Guest User

Untitled

a guest
Mar 6th, 2017
99
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SAS 12.57 KB | None | 0 0
  1. /*
  2.  ______ _____            _   _   _        _     _          
  3.  |  ____|  __ \     /\   | \ | | | |      | |   | |          
  4.  | |__  | |__) |   /  \  |  \| | | |_ __ _| |__ | | ___  ___
  5.  |  __| |  _  /   / /\ \ | . ` | | __/ _` | '_ \| |/ _ \/ __|
  6.  | |    | | \ \  / ____ \| |\  | | || (_| | |_) | |  __/\__ \
  7.  |_|    |_|  \_\/_/    \_\_|_\_|  \__\__,_|_.__/|_|\___||___/
  8.  | |           (_)         | (_)         | |                
  9.  | |__  _   _   _ _ __   __| |_  ___ __ _| |_ ___  _ __ ___  
  10.  | '_ \| | | | | | '_ \ / _` | |/ __/ _` | __/ _ \| '__/ __|
  11.  | |_) | |_| | | | | | | (_| | | (_| (_| | || (_) | |  \__ \
  12.  |_.__/ \__, | |_|_| |_|\__,_|_|\___\__,_|\__\___/|_|  |___/
  13.          __/ |                                              
  14.         |___/                                                
  15. */
  16.  
  17. /********************************/
  18. /*                              */
  19. /*    Other tables from FRAN    */
  20. /*  for the sake of reporting   */
  21. /*                              */
  22. /********************************/
  23.  
  24. /************************************************************/
  25. /*  5. Yearly, quarterly or monthly data for one indicator  */
  26. /*                                           !!!!!!!!!!!!!  */
  27. /************************************************************/
  28. /*          Put it into another ordered list            */
  29. /*      Prompt for selecting number of years back       */
  30. /*                  Take values from time               */
  31. /********************************************************/
  32.  
  33. %macro ind_rename;
  34.     %if &idc=_IBC1A_ %then %let idc2="IBC-1A";
  35.     %if &idc=_ILL3_ %then %let idc2="ILL-3";
  36.     %if &idc=_REF4_ %then %let idc2="REF-4";
  37.     %if &idc=_ASY5_ %then %let idc2="ASY-5";
  38.     %if &idc=_RET7A_ %then %let idc2="RET-7A";
  39.     %if &idc=_RET7B_ %then %let idc2="RET-7B";
  40.     %if &idc=_FAL6_ %then %let idc2="FAL-6";
  41.     %if &idc=_FAC2_ %then %let idc2="FAC-2";
  42.     %if &idc=_IBC1B_ %then %let idc2="IBC-1B";
  43.     %put &idc2.;
  44. %mend ind_rename;
  45.  
  46. /*  Nationalities with another variable: member state or border section */
  47.  
  48. %macro ind_select;
  49.  
  50. %ind_rename;
  51.  
  52. /*  Totals over years for chosen indicator, nationality and 2nd grouping variable   */
  53. proc sql;
  54.     create table &tmplib..Yots1&suffix&idc as
  55.     select distinct Indicator, &variable, &variable2, YEAR, sum(Total) as sum_Total
  56.     from &inlib..&set2.
  57.     where Indicator=&idc2 and YEAR in (&yrs)
  58.     group by Indicator, YEAR, &variable, &variable2
  59.     order by &variable, &variable2, YEAR;
  60. quit;
  61.  
  62. /*  Totals over quarters for chosen indicator, nationality and 2nd grouping variable    */
  63. proc sql;
  64.     create table &tmplib..Qots1&suffix&idc as
  65.     select distinct Indicator, &variable, &variable2, YYYYQ, sum(Total) as sum_Total
  66.     from &inlib..&set2.
  67.     where Indicator=&idc2 and YYYYQ in (&qtrs)
  68.     group by Indicator, YYYYQ, &variable, &variable2
  69.     order by &variable, &variable2, YYYYQ;
  70. quit;
  71.  
  72. /*  Totals over months for chosen indicator, nationality and 2nd grouping variable  */
  73. proc sql;
  74.     create table &tmplib..Mots1&suffix&idc as
  75.     select distinct Indicator, &variable, &variable2, YYYYMM, sum(Total) as sum_Total
  76.     from &inlib..&set2.
  77.     where Indicator=&idc2 and YYYYMM in (&mths)
  78.     group by Indicator, YYYYMM, &variable, &variable2
  79.     order by &variable, &variable2, YYYYMM;
  80. quit;
  81.  
  82. /* Rename the months */
  83. data &tmplib..Mots1&suffix&idc;
  84. set &tmplib..Mots1&suffix&idc;
  85.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  86. run;
  87.  
  88. /************************************************************/
  89. /* Reshape the dataset so that it looks the way we need.... */
  90. /************************************************************/
  91.  
  92. /* Yearly */
  93. proc transpose data=&tmplib..Yots1&suffix&idc prefix=v
  94.     out=&tmplib..Yots1&suffix&idc._tr(drop=_NAME_);
  95.     by Indicator &variable &variable2;
  96.     id YEAR;
  97.     var sum_Total;
  98. run;
  99.  
  100. /* Quarterly */
  101. proc transpose data=&tmplib..Qots1&suffix&idc prefix=v
  102.     out=&tmplib..Qots1&suffix&idc._tr(drop=_NAME_);
  103.     by Indicator &variable &variable2;
  104.     id YYYYQ;
  105.     var sum_Total;
  106. run;
  107.  
  108. /* Monthly */
  109. proc transpose data=&tmplib..Mots1&suffix&idc prefix=v
  110.     out=&tmplib..Mots1&suffix&idc._tr(drop=_NAME_);
  111.     by Indicator &variable &variable2;
  112.     id YYYYMM;
  113.     var sum_Total;
  114. run;
  115.  
  116. /********************************************************/
  117. /*  Merge Yearly with Quarterly and Yearly with Monthly */
  118. /*                  Reorder variables                   */
  119. /********************************************************/
  120.  
  121. /*  Yearly + Quarterly  */
  122. data &tmplib..YQots1&suffix&idc._tr;
  123. merge &tmplib..Yots1&suffix&idc._tr &tmplib..Qots1&suffix&idc._tr;
  124. by Indicator &variable &variable2;
  125. run;
  126.  
  127. /*  Yearly + Monthly    */
  128. data &tmplib..YMots1&suffix&idc._tr;
  129. merge &tmplib..Yots1&suffix&idc._tr &tmplib..Mots1&suffix&idc._tr;
  130. by Indicator &variable &variable2;
  131. run;
  132.  
  133. /****************************************************************************/
  134. /*  Select top 10 combinations by latest quarter/month, aggregate others    */
  135. /****************************************************************************/
  136.  
  137. /*Latest quarter*/
  138. %global Q;
  139. %let Q=Q;
  140. %let v=v;
  141. %global latestq;
  142. %let latestq=&v&end&Q&eq;
  143. %let firsty=&v&start;
  144.  
  145. /********************************/
  146. /* Sort by latest quarter/month */
  147. /********************************/
  148.  
  149. proc sort Data=&tmplib..YQots1&suffix&idc._tr ;
  150. by descending &latestq;
  151. run;
  152.  
  153. %lm;
  154.  
  155. proc sort Data=&tmplib..YMots1&suffix&idc._tr ;
  156. by descending &latestm;
  157. run;
  158.  
  159. /**********************/
  160. /*  Choose top n rows */
  161. /**********************/
  162.  
  163. data &tmplib..YQots1&suffix&idc._tr;
  164. set &tmplib..YQots1&suffix&idc._tr;
  165. count + 1;
  166. by Indicator;
  167. if first.Indicator then count = 1;
  168. run;
  169.  
  170. data &tmplib..YMots1&suffix&idc._tr;
  171. set &tmplib..YMots1&suffix&idc._tr;
  172. count + 1;
  173. by Indicator;
  174. if first.Indicator then count = 1;
  175. run;
  176.  
  177. /* Top n rows */
  178. data &tmplib..YQots1&suffix&idc._tr_topn &tmplib..YQots1&suffix&idc._tr_others;
  179. set &tmplib..YQots1&suffix&idc._tr;
  180.     if count<=&n then output &tmplib..YQots1&suffix&idc._tr_topn;
  181.     if count>&n then output &tmplib..YQots1&suffix&idc._tr_others;
  182. run;
  183.  
  184. data &tmplib..YMots1&suffix&idc._tr_topn &tmplib..YMots1&suffix&idc._tr_others;
  185. set &tmplib..YMots1&suffix&idc._tr;
  186.     if count<=&n then output &tmplib..YMots1&suffix&idc._tr_topn;
  187.     if count>&n then output &tmplib..YMots1&suffix&idc._tr_others;
  188. run;
  189.  
  190. /*  Aggregate others    */
  191. proc means data=&tmplib..YQots1&suffix&idc._tr_others noprint;
  192. class Indicator;
  193. var &firsty--&latestq;
  194. output out=&tmplib..YQots1&suffix&idc._tr_othersagg sum=;
  195. run;
  196.  
  197. proc means data=&tmplib..YMots1&suffix&idc._tr_others noprint;
  198. class Indicator;
  199. var &firsty--&latestm;
  200. output out=&tmplib..YMots1&suffix&idc._tr_othersagg sum=;
  201. run;
  202.  
  203. data &tmplib..YQots1&suffix&idc._tr_othersagg;
  204. set &tmplib..YQots1&suffix&idc._tr_othersagg;
  205. where Indicator ne "";
  206. drop _TYPE_ _FREQ_;
  207. &variable="others";
  208. &variable2="others";
  209. run;
  210.  
  211. data &tmplib..YMots1&suffix&idc._tr_othersagg;
  212. set &tmplib..YMots1&suffix&idc._tr_othersagg;
  213. where Indicator ne "";
  214. drop _TYPE_ _FREQ_;
  215. &variable="others";
  216. &variable2="others";
  217. run;
  218.  
  219. /*  Append tables   */
  220. data &tmplib..YQots1&suffix&idc._tr_final;
  221. set &tmplib..YQots1&suffix&idc._tr_topn &tmplib..YQots1&suffix&idc._tr_othersagg;
  222. drop count;
  223. run;
  224.  
  225. data &tmplib..YMots1&suffix&idc._tr_final;
  226. set &tmplib..YMots1&suffix&idc._tr_topn &tmplib..YMots1&suffix&idc._tr_othersagg;
  227. drop count;
  228. run;
  229.  
  230. /************************/
  231. /*  Reorder variables   */
  232. /************************/
  233.  
  234. data &tmplib..YQots1&suffix&idc._tr_final;
  235. retain Indicator &variable &variable2 &qorder;
  236. set &tmplib..YQots1&suffix&idc._tr_final;
  237. run;
  238.  
  239. data &tmplib..YMots1&suffix&idc._tr_final;
  240. retain Indicator &variable &variable2 &morder;
  241. set &tmplib..YMots1&suffix&idc._tr_final;
  242. run;
  243.  
  244. /*********************************/
  245. /* Replace missing values with 0 */
  246. /*********************************/
  247.  
  248. /* Yearly with Quarterly */
  249. data &tmplib..YQots1&suffix&idc._tr_final(drop=i);
  250. set &tmplib..YQots1&suffix&idc._tr_final;                                                            
  251. array testmiss(*) _numeric_;                                            
  252. do i = 1 to dim(testmiss);                                              
  253.     if testmiss(i)=. then testmiss(i)=0;                                    
  254. end;                                                                    
  255. run;
  256.  
  257. /* Yearly with Monthly */
  258. data &tmplib..YMots1&suffix&idc._tr_final(drop=i);
  259. set &tmplib..YMots1&suffix&idc._tr_final;                                                            
  260. array testmiss(*) _numeric_;                                            
  261. do i = 1 to dim(testmiss);                                              
  262.     if testmiss(i)=. then testmiss(i)=0;                                    
  263. end;                                                                    
  264. run;
  265.  
  266. %mend ind_select;
  267.  
  268. /*  Tables to be exported   */
  269. /*  */
  270.  
  271. /*  Select main variable - nat_pers_label from prompt to FRAN_2_time    */
  272. %global variable;
  273. %let variable=&vari;
  274.  
  275. %time_secure;
  276.  
  277. /*  Final year  */
  278. %global end;
  279. %let end=&mt1;
  280.  
  281. /*  Quarters    */
  282. %global bq;
  283. %let bq=1;
  284. *%put &qtr1.;
  285. *%put &bq.;
  286. %global eq;
  287. %let eq=&mt2;
  288. *%put &qtr2.;
  289. *%put &eq.;
  290.  
  291. /*  Months  */
  292. %global bm;
  293. %let bm=1;
  294. *%put &mth1.;
  295. *%put &bm.;
  296. %global em;
  297. %let em=&mt3;
  298. *%put &mth2.;
  299. *%put &em.;
  300.  
  301. %years(start=&start,end=&end);
  302. %quarters(start=&start,end=&end,bq=&bq,eq=&eq);
  303. %months(start=&start,end=&end,bm=&bm,em=&em);
  304. %qtr_order;
  305. %mth_order;
  306.  
  307. %global idc;
  308. %global idc2;
  309. %global variable2;
  310. %global suffix;
  311.  
  312. /************************/
  313. /*  V1: member states   */
  314. /************************/
  315. %let variable2=reporting_country_label;
  316. %let suffix=RC;
  317. %global n;
  318. %let n=10;
  319.  
  320. /*  Run macro ind_select separately per each indicator  */
  321. %let idc=_IBC1A_;
  322. %ind_select;
  323.  
  324. %let idc=_ILL3_;
  325. %ind_select;
  326.  
  327. %let idc=_REF4_;
  328. %ind_select;
  329.  
  330. %let idc=_ASY5_;
  331. %ind_select;
  332.  
  333. %let idc=_RET7A_;
  334. %ind_select;
  335.  
  336. %let idc=_RET7B_;
  337. %ind_select;
  338.  
  339. %let idc=_FAL6_;
  340. %ind_select;
  341.  
  342. %let idc=_FAC2_;
  343. %ind_select;
  344.  
  345. %let idc=_IBC1B_;
  346. %ind_select;
  347.  
  348. /************************/
  349. /*  V2: border section  */
  350. /************************/
  351. %let variable2=KeyBorderSection;
  352. %let suffix=KBS;
  353. %global n;
  354. %let n=10;
  355.  
  356. /*  Run macro ind_select separately per each indicator  */
  357. %let idc=_IBC1A_;
  358. %ind_select;
  359.  
  360. %let idc=_ILL3_;
  361. %ind_select;
  362.  
  363. %let idc=_REF4_;
  364. %ind_select;
  365.  
  366. %let idc=_ASY5_;
  367. %ind_select;
  368.  
  369. %let idc=_RET7A_;
  370. %ind_select;
  371.  
  372. %let idc=_RET7B_;
  373. %ind_select;
  374.  
  375. %let idc=_FAL6_;
  376. %ind_select;
  377.  
  378. %let idc=_FAC2_;
  379. %ind_select;
  380.  
  381. %let idc=_IBC1B_;
  382. %ind_select;
  383.  
  384. /****************************************************************************************************/
  385. /*
  386. ___________.__    .__         .__           __  .__             ___________ _______  ________ ._._._.
  387. \__    ___/|  |__ |__| ______ |__| ______ _/  |_|  |__   ____   \_   _____/ \      \ \______ \| | | |
  388.   |    |   |  |  \|  |/  ___/ |  |/  ___/ \   __\  |  \_/ __ \   |    __)_  /   |   \ |    |  \ | | |
  389.   |    |   |   Y  \  |\___ \  |  |\___ \   |  | |   Y  \  ___/   |        \/    |    \|    `   \|\|\|
  390.   |____|   |___|  /__/____  > |__/____  >  |__| |___|  /\___  > /_______  /\____|__  /_______  /_____
  391.                 \/        \/          \/             \/     \/          \/         \/        \/\/\/\/
  392. */
  393. /*****************************************************************************************************/
  394.  
  395. /*
  396.  _____                _           _   _                                  
  397. /  __ \              | |         | | | |                                  
  398. | /  \/_ __ ___  __ _| |_ ___  __| | | |__  _   _                        
  399. | |   | '__/ _ \/ _` | __/ _ \/ _` | | '_ \| | | |                        
  400. | \__/\ | |  __/ (_| | ||  __/ (_| | | |_) | |_| |                        
  401.  \____/_|  \___|\__,_|\__\___|\__,_| |_.__/ \__, |                        
  402.                                              __/ |                        
  403.                                             |___/                        
  404.  _   __                      _   _____ _                          _    _  
  405. | | / /                     | | |  _  | |                        | |  (_)
  406. | |/ /  ___  _ __ _ __   ___| | | | | | |___ _________      _____| | ___  
  407. |    \ / _ \| '__| '_ \ / _ \ | | | | | / __|_  / _ \ \ /\ / / __| |/ / |
  408. | |\  \ (_) | |  | | | |  __/ | \ \_/ / \__ \/ /  __/\ V  V /\__ \   <| |
  409. \_| \_/\___/|_|  |_| |_|\___|_|  \___/|_|___/___\___| \_/\_/ |___/_|\_\_|                                                      
  410.                                                                                                                                                                                                                                                                                                                                                                                                                                          
  411. */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement