Advertisement
Guest User

Untitled

a guest
Mar 6th, 2017
144
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SAS 75.60 KB | None | 0 0
  1. /*
  2.   ______ _____            _   _   _        _     _                  
  3.  |  ____|  __ \     /\   | \ | | | |      | |   | |                
  4.  | |__  | |__) |   /  \  |  \| | | |_ __ _| |__ | | ___  ___        
  5.  |  __| |  _  /   / /\ \ | . ` | | __/ _` | '_ \| |/ _ \/ __|      
  6.  | |    | | \ \  / ____ \| |\  | | || (_| | |_) | |  __/\__ \      
  7.  |_|    |_|  \_\/_/    \_\_| \_|  \__\__,_|_.__/|_|\___||___/      
  8.    __             _                     _                       _  
  9.   / _|           (_)                   (_)                     | |  
  10.  | |_ ___  _ __   _ _ __ ___  _ __ ___  _  __ _ _ __ __ _ _ __ | |_
  11.  |  _/ _ \| '__| | | '_ ` _ \| '_ ` _ \| |/ _` | '__/ _` | '_ \| __|
  12.  | || (_) | |    | | | | | | | | | | | | | (_| | | | (_| | | | | |_
  13.  |_| \___/|_|    |_|_| |_| |_|_| |_| |_|_|\__, |_|  \__,_|_| |_|\__|
  14.                                            __/ |                    
  15.               _   _                   _ _ |___/                    
  16.              | | (_)                 | (_) |                        
  17.   _ __   __ _| |_ _  ___  _ __   __ _| |_| |_ _   _                
  18.  | '_ \ / _` | __| |/ _ \| '_ \ / _` | | | __| | | |                
  19.  | | | | (_| | |_| | (_) | | | | (_| | | | |_| |_| |                
  20.  |_| |_|\__,_|\__|_|\___/|_| |_|\__,_|_|_|\__|\__, |                
  21.                                                __/ |                
  22.                                               |___/                
  23. */
  24.  
  25. /* Choose indicator => ok */
  26. %global ind;
  27. %let ind="IBC-1A","IBC-1B","FAC-2","ILL-3","REF-4","ASY-5","FAL-6","RET-7A","RET-7B";
  28.  
  29. *%put &ind.;
  30. /* Temporary library => ok*/
  31. *%global tmplib;
  32. *%let tmplib=work;
  33.  
  34. *%let tmplib=dane;
  35. *libname &tmplib '\\aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\INTERNSHIP\Kornel\Library';
  36.  
  37. /* Choose dataset - most probably will be set fixed => ok */
  38. *%global set;
  39. *%let set=temp;
  40.  
  41. /*  FRAN    */
  42. *%global inlib;
  43. *%let inlib=shredstg;
  44. *%global set2;
  45. *%let set2=fran_analysis;
  46. *libname &inlib '\\aofrontex.local\sas-shareddrive\SASData';
  47.  
  48.  
  49. /********************************************************************************/
  50. /*                                                                              */
  51. /*  Yearly data for nationality - yearly shares in all immigrant nationalities  */
  52. /*                                                                              */
  53. /********************************************************************************/
  54.  
  55. %time_secure;
  56.  
  57. %let start=%eval(&mt1-&step);
  58. %global end;
  59. %let end=&mt1;
  60.  
  61. *%put &start.;
  62. *%put &end.;
  63.  
  64.  
  65. /* Choose time span - yearly*/
  66. %years(start=&start,end=&end);
  67.  
  68. /* Totals over years for chosen variable - nationality and chosen level */
  69.  
  70. proc sql noprint;
  71.     create table &tmplib..Ystep1var as
  72.     select distinct &variable, YEAR,Indicator, sum(Total) as sum_Total
  73.     from &inlib..&set2.
  74.     where &variable="&val" and YEAR in (&yrs) and Indicator ne ""
  75.     group by &variable, YEAR, Indicator
  76.     order by Indicator, YEAR;
  77. quit;
  78.  
  79. /* Reshape the dataset so that it looks the way we need.... */
  80. proc transpose data=&tmplib..Ystep1var  prefix=v
  81.     out=&tmplib..Ystep1var_tr;
  82.     by Indicator;
  83.     id YEAR;
  84.     var sum_Total;
  85. run;
  86.  
  87. data &tmplib..Ystep1var_tr;
  88. set &tmplib..Ystep1var_tr;
  89. drop _NAME_;
  90. run;
  91.  
  92. /* Totals over years for all nationalities - chosen indicators*/
  93. proc sql noprint;
  94.     create table &tmplib..Ystep2all as
  95.     select distinct YEAR,Indicator, sum(Total) as sum_Total
  96.     from &inlib..&set2.
  97.     where YEAR in (&yrs) and Indicator ne ""
  98.     group by YEAR, Indicator
  99.     order by Indicator, YEAR;
  100. quit;
  101.  
  102. /* Calculate the shares we need */
  103. proc sql noprint;
  104.     create table &tmplib..Yshares as
  105.     select distinct a.Indicator, a.&variable, a.YEAR, a.sum_Total/b.sum_Total as Share format=percentn10.2
  106.     from &tmplib..Ystep1var as a, &tmplib..Ystep2all as b
  107.     where a.YEAR=b.YEAR and a.Indicator=b.Indicator
  108.     order by Indicator, YEAR, &variable;
  109. quit;
  110.  
  111. /* Reshape the dataset so that it looks the way we need.... */
  112. proc transpose data=&tmplib..Yshares name=share prefix=v
  113.     out=&tmplib..Yshares_tr;
  114.     by Indicator;
  115.     id YEAR;
  116.     var Share;
  117. run;
  118.  
  119. /* Put the chosen variable at the beginning of the dataset */
  120. data &tmplib..Yshares_tr;
  121. set &tmplib..Yshares_tr;
  122. drop share;
  123. &variable="&val";
  124. run;
  125.  
  126. data &tmplib..Yshares_tr;
  127. retain &variable;
  128. set &tmplib..Yshares_tr;
  129. run;
  130.  
  131. /* Tested - works for years */
  132.  
  133. /************************************************************************/
  134. /*                                                                      */
  135. /*              Divide yearly totals by another variable                */
  136. /*                                                                      */
  137. /************************************************************************/
  138.  
  139. /*********************/
  140. /* Reporting_country */
  141. /*********************/
  142.  
  143. /* Choose 2nd dividing variable => ok */
  144. %global variable2;
  145. %global suffix;
  146. %let variable2=reporting_country_label;
  147. %let suffix=RC;
  148.  
  149. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  150. proc sql noprint;
  151.     create table &tmplib..Ystep1var&suffix as
  152.     select distinct &variable,YEAR,Indicator,&variable2,sum(Total) as sum_Total
  153.     from &inlib..&set2.
  154.     where &variable="&val" and YEAR in (&yrs) and Indicator ne ""
  155.     group by &variable, YEAR, Indicator, &variable2
  156.     order by Indicator, &variable2, YEAR;
  157. quit;
  158.  
  159. /* Reshape the dataset so that it looks the way we need.... */
  160. proc transpose data=&tmplib..Ystep1var&suffix  prefix=v
  161.     out=&tmplib..Ystep1var&suffix._tr;
  162.     by Indicator &variable2;
  163.     id YEAR;
  164.     var sum_Total;
  165. run;
  166.  
  167. /* Insert the chosen variable into dataset */
  168. data &tmplib..Ystep1var&suffix._tr;
  169. set &tmplib..Ystep1var&suffix._tr;
  170.     &variable="&val";
  171. run;
  172.  
  173. /* Put the chosen variable at the beginning os the dataset */
  174. data &tmplib..Ystep1var&suffix._tr;
  175. retain &variable;
  176. set &tmplib..Ystep1var&suffix._tr;
  177. drop _NAME_;
  178. run;
  179.  
  180. /************************/
  181. /*  KeyBorderSection    */
  182. /************************/
  183.  
  184. /* Choose 2nd dividing variable => ok */
  185. %let variable2=KeyBorderSection;
  186. %let suffix=KBS;
  187.  
  188. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  189. proc sql noprint;
  190.     create table &tmplib..Ystep1var&suffix as
  191.     select distinct &variable,YEAR,Indicator,&variable2,sum(Total) as sum_Total
  192.     from &inlib..&set2.
  193.     where &variable="&val" and YEAR in (&yrs) and Indicator ne ""
  194.     group by &variable, YEAR, Indicator, &variable2
  195.     order by Indicator, &variable2, YEAR;
  196. quit;
  197.  
  198. /* Reshape the dataset so that it looks the way we need.... */
  199. proc transpose data=&tmplib..Ystep1var&suffix  prefix=v
  200.     out=&tmplib..Ystep1var&suffix._tr;
  201.     by Indicator &variable2;
  202.     id YEAR;
  203.     var sum_Total;
  204. run;
  205.  
  206. /* Insert the chosen variable into dataset */
  207. data &tmplib..Ystep1var&suffix._tr;
  208. set &tmplib..Ystep1var&suffix._tr;
  209.     &variable="&val";
  210. run;
  211.  
  212. /* Put the chosen variable at the beginning os the dataset */
  213. data &tmplib..Ystep1var&suffix._tr;
  214. retain &variable;
  215. set &tmplib..Ystep1var&suffix._tr;
  216. drop _NAME_;
  217. run;
  218.  
  219. /******************/
  220. /* BorderLocation */
  221. /******************/
  222.  
  223. /* Choose 2nd dividing variable => ok */
  224. %let variable2=BorderLocation;
  225. %let suffix=BL;
  226.  
  227. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  228. proc sql noprint;
  229.     create table &tmplib..Ystep1var&suffix as
  230.     select distinct &variable,YEAR,Indicator,&variable2,sum(Total) as sum_Total
  231.     from &inlib..&set2.
  232.     where &variable="&val" and YEAR in (&yrs) and Indicator ne "" and &variable2 ne ""
  233.     group by &variable, YEAR, Indicator, &variable2
  234.     order by Indicator, &variable2, YEAR, sum_Total;
  235. quit;
  236.  
  237. /*  Change empty Border Locations to Inland */
  238. data &tmplib..Ystep1var&suffix;
  239. set &tmplib..Ystep1var&suffix;
  240.     if &variable2 eq "" then &variable2="Inland";
  241. run;
  242.  
  243. /* Reshape the dataset so that it looks the way we need.... */
  244. proc transpose data=&tmplib..Ystep1var&suffix prefix=v
  245.     out=&tmplib..Ystep1var&suffix._tr;
  246.     by Indicator &variable2;
  247.     id YEAR;
  248.     var sum_Total;
  249. run;
  250.  
  251. /* Insert the chosen variable into dataset */
  252. data &tmplib..Ystep1var&suffix._tr;
  253. set &tmplib..Ystep1var&suffix._tr;
  254.     &variable="&val";
  255. run;
  256.  
  257. /* Put the chosen variable at the beginning os the dataset */
  258. data &tmplib..Ystep1var&suffix._tr;
  259. retain &variable;
  260. set &tmplib..Ystep1var&suffix._tr;
  261. drop _NAME_;
  262. run;
  263.  
  264. /********************************************/
  265. /*  BorderLocation by top n MemberStates    */
  266. /********************************************/
  267.  
  268. /*  Choose 3rd dividing variable => ok  */
  269. %global variable3;
  270. %let variable3=reporting_country_label;
  271. %let suffix2=RC;
  272.  
  273. /* Totals over years for chosen variable - nationality by variable3 - reporting country */
  274. proc sql noprint;
  275.     create table &tmplib..Ystep1var&suffix._&suffix2 as
  276.     select distinct &variable,YEAR,Indicator,&variable2,&variable3,sum(Total) as sum_Total
  277.     from &inlib..&set2.
  278.     where &variable="&val" and YEAR in (&yrs) and Indicator ne "" and &variable2 ne ""
  279.     group by &variable, YEAR, Indicator, &variable2, &variable3
  280.     order by Indicator, &variable2, &variable3, YEAR, sum_Total;
  281. quit;
  282.  
  283. /*  Change empty Border Locations to Inland */
  284. data &tmplib..Ystep1var&suffix._&suffix2;
  285. set &tmplib..Ystep1var&suffix._&suffix2;
  286.     if &variable2 eq "" then &variable2="Inland";
  287. run;
  288.  
  289. /* Reshape the dataset so that it looks the way we need.... */
  290. proc transpose data=&tmplib..Ystep1var&suffix._&suffix2 prefix=v
  291.     out=&tmplib..Ystep1var&suffix._&suffix2._tr;
  292.     by Indicator &variable2 &variable3;
  293.     id YEAR;
  294.     var sum_Total;
  295. run;
  296.  
  297. /* Insert the chosen variable into dataset */
  298. data &tmplib..Ystep1var&suffix._&suffix2._tr;
  299. set &tmplib..Ystep1var&suffix._&suffix2._tr;
  300.     &variable="&val";
  301. run;
  302.  
  303. /* Put the chosen variable at the beginning os the dataset */
  304. data &tmplib..Ystep1var&suffix._&suffix2._tr;
  305. retain &variable;
  306. set &tmplib..Ystep1var&suffix._&suffix2._tr;
  307. drop _NAME_;
  308. run;
  309.  
  310. /************************************************************************************/
  311. /*                                                                                  */
  312. /*  Quaterly data for nationality - quaterly shares in all immigrant nationalities  */
  313. /*                                                                                  */
  314. /************************************************************************************/
  315.  
  316. %global bq;
  317. %let bq=1;
  318. *%put &qtr1.;
  319. *%put &bq.;
  320. %global eq;
  321. %let eq=&mt2;
  322. *%put &qtr2.;
  323. *%put &eq.;
  324.  
  325. /* Choose time span */
  326. %quarters(start=&start,end=&end,bq=&bq,eq=&eq);
  327.  
  328. /* Totals over quartesrs for chosen variable - nationality and chosen level */
  329. proc sql noprint;
  330.     create table &tmplib..Qstep1var as
  331.     select distinct &variable, YYYYQ,Indicator, sum(Total) as sum_Total
  332.     from &inlib..&set2.
  333.     where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
  334.     group by &variable, YYYYQ, Indicator
  335.     order by Indicator, YYYYQ;
  336. quit;
  337.  
  338. /* Reshape the dataset so that it looks the way we need.... */
  339. proc transpose data=&tmplib..Qstep1var  prefix=v
  340.     out=&tmplib..Qstep1var_tr;
  341.     by Indicator;
  342.     id YYYYQ;
  343.     var sum_Total;
  344. run;
  345.  
  346. data &tmplib..Qstep1var_tr;
  347. set &tmplib..Qstep1var_tr;
  348. drop _NAME_;
  349. run;
  350.  
  351. /* Totals over quarters for all nationalities */
  352. proc sql noprint;
  353.     create table &tmplib..Qstep2all as
  354.     select distinct Indicator, YYYYQ, sum(Total) as sum_Total
  355.     from &inlib..&set2.
  356.     where YYYYQ in (&qtrs) and Indicator ne ""
  357.     group by YYYYQ, Indicator
  358.     order by Indicator, YYYYQ;
  359. quit;
  360.  
  361. /* Calculate the shares we need */
  362. proc sql noprint;
  363.     create table &tmplib..Qshares as
  364.     select distinct a.Indicator, a.&variable, a.YYYYQ, a.sum_Total/b.sum_Total as Share format=percentn10.2
  365.     from &tmplib..Qstep1var as a, &tmplib..Qstep2all as b
  366.     where a.YYYYQ=b.YYYYQ and a.Indicator=b.Indicator
  367.     order by Indicator, YYYYQ;
  368. quit;
  369.  
  370. /* Reshape the dataset so that it looks the way we need.... */
  371. proc transpose data=&tmplib..Qshares name=share prefix=v
  372.     out=&tmplib..Qshares_tr;
  373.     by Indicator;
  374.     id YYYYQ;
  375.     var Share;
  376. run;
  377.  
  378. /* Put the chosen variable at the beginning of the dataset */
  379. data &tmplib..Qshares_tr;
  380. set &tmplib..Qshares_tr;
  381. drop share;
  382. &variable="&val";
  383. run;
  384.  
  385. data &tmplib..Qshares_tr;
  386. retain &variable;
  387. set &tmplib..Qshares_tr;
  388. run;
  389.  
  390. /* Tested - works for quarters */
  391.  
  392. /************************************************************************/
  393. /*                                                                      */
  394. /*              Divide quarterly totals by another variable             */
  395. /*                                                                      */
  396. /************************************************************************/
  397.  
  398. /*********************/
  399. /* Reporting_country */
  400. /*********************/
  401.  
  402. /* Choose 2nd dividing variable => ok */
  403. %let variable2=reporting_country_label;
  404. %let suffix=RC;
  405.  
  406. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  407. proc sql noprint;
  408.     create table &tmplib..Qstep1var&suffix as
  409.     select distinct &variable,YYYYQ,Indicator,&variable2,sum(Total) as sum_Total
  410.     from &inlib..&set2.
  411.     where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
  412.     group by &variable, YYYYQ, Indicator, &variable2
  413.     order by Indicator, &variable2;
  414. quit;
  415.  
  416. /* Reshape the dataset so that it looks the way we need.... */
  417. proc transpose data=&tmplib..Qstep1var&suffix prefix=v
  418.     out=&tmplib..Qstep1var&suffix._tr;
  419.     by Indicator &variable2;
  420.     id YYYYQ;
  421.     var sum_Total;
  422. run;
  423.  
  424. data &tmplib..Qstep1var&suffix._tr;
  425. set &tmplib..Qstep1var&suffix._tr;
  426. drop _NAME_;
  427. run;
  428.  
  429. /************************/
  430. /*  KeyBorderSection    */
  431. /************************/
  432.  
  433. /* Choose 2nd dividing variable => ok */
  434. %let variable2=KeyBorderSection;
  435. %let suffix=KBS;
  436.  
  437. /* Totals over years for chosen variable - nationality by variable2 - BorderLocation */
  438. proc sql noprint;
  439.     create table &tmplib..Qstep1var&suffix as
  440.     select distinct &variable,YYYYQ,Indicator,&variable2,sum(Total) as sum_Total
  441.     from &inlib..&set2.
  442.     where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
  443.     group by &variable, YYYYQ, Indicator, &variable2
  444.     order by Indicator, &variable2, YYYYQ;
  445. quit;
  446.  
  447. /* Reshape the dataset so that it looks the way we need.... */
  448. proc transpose data=&tmplib..Qstep1var&suffix prefix=v
  449.     out=&tmplib..Qstep1var&suffix._tr;
  450.     by Indicator &variable2;
  451.     id YYYYQ;
  452.     var sum_Total;
  453. run;
  454.  
  455. /* Put the chosen variable at the beginning os the dataset */
  456. data &tmplib..Qstep1var&suffix._tr;
  457. set &tmplib..Qstep1var&suffix._tr;
  458. drop _NAME_;
  459. run;
  460.  
  461. /******************/
  462. /* BorderLocation */
  463. /******************/
  464.  
  465. /* Choose 2nd dividing variable => ok */
  466. %let variable2=BorderLocation;
  467. %let suffix=BL;
  468.  
  469. /* Totals over years for chosen variable - nationality by variable2 - BorderLocation */
  470. proc sql noprint;
  471.     create table &tmplib..Qstep1var&suffix as
  472.     select distinct &variable,YYYYQ,Indicator,&variable2,sum(Total) as sum_Total
  473.     from &inlib..&set2.
  474.     where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
  475.     group by &variable, YYYYQ, Indicator, &variable2
  476.     order by Indicator, &variable2, YYYYQ;
  477. quit;
  478.  
  479. /*  Change empty Border Locations to Inland */
  480. data &tmplib..Qstep1var&suffix;
  481. set &tmplib..Qstep1var&suffix;
  482.     if &variable2 eq "" then &variable2="Inland";
  483. run;
  484.  
  485. /* Reshape the dataset so that it looks the way we need.... */
  486. proc transpose data=&tmplib..Qstep1var&suffix prefix=v
  487.     out=&tmplib..Qstep1var&suffix._tr;
  488.     by Indicator &variable2;
  489.     id YYYYQ;
  490.     var sum_Total;
  491. run;
  492.  
  493. /* Put the chosen variable at the beginning os the dataset */
  494. data &tmplib..Qstep1var&suffix._tr;
  495. set &tmplib..Qstep1var&suffix._tr;
  496. drop _NAME_;
  497. run;
  498.  
  499. /********************************************/
  500. /*  BorderLocation by top n MemberStates    */
  501. /********************************************/
  502.  
  503. /*  Choose 3rd dividing variable => ok  */
  504. %let variable3=reporting_country_label;
  505. %let suffix2=RC;
  506.  
  507. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  508. proc sql noprint;
  509.     create table &tmplib..Qstep1var&suffix._&suffix2 as
  510.     select distinct &variable,YYYYQ,Indicator,&variable2,&variable3,sum(Total) as sum_Total
  511.     from &inlib..&set2.
  512.     where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
  513.     group by &variable, YYYYQ, Indicator, &variable2, &variable3
  514.     order by Indicator, &variable2, &variable3, YYYYQ, sum_Total;
  515. quit;
  516.  
  517. /*  Change empty Border Locations to Inland */
  518. data &tmplib..Qstep1var&suffix._&suffix2;
  519. set &tmplib..Qstep1var&suffix._&suffix2;
  520.     if &variable2 eq "" then &variable2="Inland";
  521. run;
  522.  
  523. /* Reshape the dataset so that it looks the way we need.... */
  524. proc transpose data=&tmplib..Qstep1var&suffix._&suffix2 prefix=v
  525.     out=&tmplib..Qstep1var&suffix._&suffix2._tr;
  526.     by Indicator &variable2 &variable3;
  527.     id YYYYQ;
  528.     var sum_Total;
  529. run;
  530.  
  531. /* Insert the chosen variable into dataset */
  532. data &tmplib..Qstep1var&suffix._&suffix2._tr;
  533. set &tmplib..Qstep1var&suffix._&suffix2._tr;
  534.     &variable="&val";
  535. run;
  536.  
  537. /************************************************************************************/
  538. /*                                                                                  */
  539. /*  Monthly data for nationality - monthly shares in all immigrant nationalities    */
  540. /*                                                                                  */
  541. /************************************************************************************/
  542.  
  543. %global bm;
  544. %let bm=1;
  545. *%put &mth1.;
  546. *%put &bm.;
  547. %global em;
  548. %let em=&mt3;
  549. *%put &mth2.;
  550. *%put &em.;
  551.  
  552. /*
  553. %global mth;
  554. %let mth=1.0;
  555. %put &mth.;
  556. %let bm=%sysfunc(putn(%sysfunc(inputn(&mth,bestw.)),bestd4.));
  557. %put &bm.;
  558. */
  559.  
  560. /* Choose time span */
  561. %months(start=&start,end=&end,bm=&bm,em=&em);
  562.  
  563. /* Totals over months for chosen nationality */
  564. proc sql noprint;
  565.     create table &tmplib..Mstep1var as
  566.     select distinct &variable, YYYYMM,Indicator, sum(Total) as sum_Total
  567.     from &inlib..&set2.
  568.     where &variable="&val" and YYYYMM in (&mths) and Indicator ne ""
  569.     group by &variable, YYYYMM, Indicator
  570.     order by Indicator, YYYYMM;
  571. quit;
  572.  
  573. data &tmplib..Mstep1var;
  574. set &tmplib..Mstep1var;
  575.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  576. run;
  577.  
  578. /*  Reshape -> long to wide */
  579. proc transpose data=&tmplib..Mstep1var prefix=v
  580.     out=&tmplib..Mstep1var_tr;
  581.     by Indicator;
  582.     id YYYYMM;
  583.     var sum_Total;
  584. run;
  585.  
  586. data &tmplib..Mstep1var_tr;
  587. set &tmplib..Mstep1var_tr;
  588. drop _NAME_;
  589. run;
  590.  
  591. /* Totals over months for all nationalities */
  592. proc sql noprint;
  593.     create table &tmplib..Mstep2all as
  594.     select distinct Indicator, YYYYMM, sum(Total) as sum_Total
  595.     from &inlib..&set2.
  596.     where YYYYMM in (&mths) and Indicator ne ""
  597.     group by YYYYMM, Indicator
  598.     order by Indicator, YYYYMM;
  599. quit;
  600.  
  601. data &tmplib..Mstep2all;
  602. set &tmplib..Mstep2all;
  603.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  604. run;
  605.  
  606. /* Calculate the shares we need */
  607. proc sql noprint;
  608.     create table &tmplib..Mshares as
  609.     select distinct a.Indicator, a.&variable, a.YYYYMM, a.sum_Total/b.sum_Total as Share format=percentn10.2
  610.     from &tmplib..Mstep1var as a, &tmplib..Mstep2all as b
  611.     where a.YYYYMM=b.YYYYMM and a.Indicator=b.Indicator
  612.     order by Indicator, YYYYMM;
  613. quit;
  614.  
  615. /* Reshape the dataset so that it looks the way we need.... */
  616. proc transpose data=&tmplib..Mshares name=share prefix=v
  617.     out=&tmplib..Mshares_tr;
  618.     by Indicator;
  619.     id YYYYMM;
  620.     var Share;
  621. run;
  622.  
  623. /* Put the chosen variable at the beginning of the dataset */
  624. data &tmplib..Mshares_tr;
  625. set &tmplib..Mshares_tr;
  626. &variable="&val";
  627. drop share;
  628. run;
  629.  
  630. data &tmplib..Mshares_tr;
  631. retain &variable;
  632. set &tmplib..Mshares_tr;
  633. run;
  634.  
  635. /* Tested - works for months */
  636.  
  637. /************************************************************************/
  638. /*                                                                      */
  639. /*              Divide monthly totals by another variable               */
  640. /*                                                                      */
  641. /************************************************************************/
  642.  
  643. /* Reporting_country */
  644.  
  645. /* Choose 2nd dividing variable => ok */
  646. %let variable2=reporting_country_label;
  647. %let suffix=RC;
  648.  
  649. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  650. proc sql noprint;
  651.     create table &tmplib..Mstep1var&suffix as
  652.     select distinct &variable,YYYYMM,Indicator,&variable2,sum(Total) as sum_Total
  653.     from &inlib..&set2.
  654.     where &variable="&val" and YYYYMM in (&mths) and Indicator ne ""
  655.     group by &variable, YYYYMM, Indicator, &variable2
  656.     order by Indicator, &variable2, YYYYMM;
  657. quit;
  658.  
  659. /* Rename the months */
  660. data &tmplib..Mstep1var&suffix;
  661. set &tmplib..Mstep1var&suffix;
  662.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  663. run;
  664.  
  665. /* Reshape the dataset so that it looks the way we need.... */
  666. proc transpose data=&tmplib..Mstep1var&suffix prefix=v
  667.     out=&tmplib..Mstep1var&suffix._tr;
  668.     by Indicator &variable2;
  669.     id YYYYMM;
  670.     var sum_Total;
  671. run;
  672.  
  673. /* Insert the chosen variable into dataset */
  674. data &tmplib..Mstep1var&suffix._tr;
  675. set &tmplib..Mstep1var&suffix._tr;
  676.     &variable="&val";
  677. run;
  678.  
  679. /* Put the chosen variable at the beginning os the dataset */
  680. data &tmplib..Mstep1var&suffix._tr;
  681. retain &variable;
  682. set &tmplib..Mstep1var&suffix._tr;
  683. drop _NAME_;
  684. run;
  685.  
  686. /*  KeyBorderSection    */
  687.  
  688. /* Choose 2nd dividing variable => ok */
  689. %let variable2=KeyBorderSection;
  690. %let suffix=KBS;
  691.  
  692. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  693. proc sql noprint;
  694.     create table &tmplib..Mstep1var&suffix as
  695.     select distinct &variable,YYYYMM,Indicator,&variable2,sum(Total) as sum_Total
  696.     from &inlib..&set2.
  697.     where &variable="&val" and YYYYMM in (&mths) and Indicator ne ""
  698.     group by &variable, YYYYMM, Indicator, &variable2
  699.     order by Indicator, &variable2, YYYYMM;
  700. quit;
  701.  
  702. /* Rename the months */
  703. data &tmplib..Mstep1var&suffix;
  704. set &tmplib..Mstep1var&suffix;
  705.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  706. run;
  707.  
  708. /* Reshape the dataset so that it looks the way we need.... */
  709. proc transpose data=&tmplib..Mstep1var&suffix prefix=v
  710.     out=&tmplib..Mstep1var&suffix._tr;
  711.     by Indicator &variable2;
  712.     id YYYYMM;
  713.     var sum_Total;
  714. run;
  715.  
  716. /* Insert the chosen variable into dataset */
  717. data &tmplib..Mstep1var&suffix._tr;
  718. set &tmplib..Mstep1var&suffix._tr;
  719.     &variable="&val";
  720. run;
  721.  
  722. /* Put the chosen variable at the beginning os the dataset */
  723. data &tmplib..Mstep1var&suffix._tr;
  724. retain &variable;
  725. set &tmplib..Mstep1var&suffix._tr;
  726. drop _NAME_;
  727. run;
  728.  
  729. /* BorderLocation */
  730.  
  731. /* Choose 2nd dividing variable => ok */
  732. %let variable2=BorderLocation;
  733. %let suffix=BL;
  734.  
  735. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  736. proc sql noprint;
  737.     create table &tmplib..Mstep1var&suffix as
  738.     select distinct &variable,YYYYMM,Indicator,&variable2,sum(Total) as sum_Total
  739.     from &inlib..&set2.
  740.     where &variable="&val" and YYYYMM in (&mths) and &variable2 ne "" and Indicator ne ""
  741.     group by &variable, YYYYMM, Indicator, &variable2
  742.     order by Indicator, &variable2, YYYYMM;
  743. quit;
  744.  
  745. /*  Change empty Border Locations to Inland */
  746. data &tmplib..Mstep1var&suffix;
  747. set &tmplib..Mstep1var&suffix;
  748.     if &variable2 eq "" then &variable2="Inland";
  749. run;
  750.  
  751. /* Rename the months */
  752. data &tmplib..Mstep1var&suffix;
  753. set &tmplib..Mstep1var&suffix;
  754.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  755. run;
  756.  
  757. /* Reshape the dataset so that it looks the way we need.... */
  758. proc transpose data=&tmplib..Mstep1var&suffix prefix=v
  759.     out=&tmplib..Mstep1var&suffix._tr;
  760.     by Indicator &variable2;
  761.     id YYYYMM;
  762.     var sum_Total;
  763. run;
  764.  
  765. /* Insert the chosen variable into dataset */
  766. data &tmplib..Mstep1var&suffix._tr;
  767. set &tmplib..Mstep1var&suffix._tr;
  768.     &variable="&val";
  769. run;
  770.  
  771. /* Put the chosen variable at the beginning os the dataset */
  772. data &tmplib..Mstep1var&suffix._tr;
  773. retain &variable;
  774. set &tmplib..Mstep1var&suffix._tr;
  775. drop _NAME_;
  776. run;
  777.  
  778. /********************************************/
  779. /*  BorderLocation by top n MemberStates    */
  780. /********************************************/
  781.  
  782. /*  Choose 3rd dividing variable => ok  */
  783. %let variable3=reporting_country_label;
  784. %let suffix2=RC;
  785.  
  786. /* Totals over years for chosen variable - nationality by variable2 - reporting country */
  787. proc sql noprint;
  788.     create table &tmplib..Mstep1var&suffix._&suffix2 as
  789.     select distinct &variable,YYYYMM,Indicator,&variable2,&variable3,sum(Total) as sum_Total
  790.     from &inlib..&set2.
  791.     where &variable="&val" and YYYYMM in (&mths) and &variable2 ne "" and Indicator ne ""
  792.     group by &variable, YYYYMM, Indicator, &variable2, &variable3
  793.     order by Indicator, &variable2, &variable3, YYYYMM, sum_Total;
  794. quit;
  795.  
  796. /* Rename the months */
  797. data &tmplib..Mstep1var&suffix._&suffix2;
  798. set &tmplib..Mstep1var&suffix._&suffix2;
  799.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  800. run;
  801.  
  802. /*  Change empty Border Locations to Inland */
  803. data &tmplib..Mstep1var&suffix._&suffix2;
  804. set &tmplib..Mstep1var&suffix._&suffix2;
  805.     if &variable2 eq "" then &variable2="Inland";
  806. run;
  807.  
  808. /* Reshape the dataset so that it looks the way we need.... */
  809. proc transpose data=&tmplib..Mstep1var&suffix._&suffix2 prefix=v
  810.     out=&tmplib..Mstep1var&suffix._&suffix2._tr;
  811.     by Indicator &variable2 &variable3;
  812.     id YYYYMM;
  813.     var sum_Total;
  814. run;
  815.  
  816. /* Insert the chosen variable into dataset */
  817. data &tmplib..Mstep1var&suffix._&suffix2._tr;
  818. set &tmplib..Mstep1var&suffix._&suffix2._tr;
  819.     &variable="&val";
  820. run;
  821.  
  822. /************************************************/
  823. /*                                              */
  824. /*  Choose top n totals for indicator, sort     */
  825. /*              aggregate the rest              */
  826. /*                                              */
  827. /************************************************/
  828.  
  829. /*
  830. latest year
  831. %let end=2012;
  832.  
  833. latest quarter
  834. %let eq=3;
  835.  
  836. 2012Q3
  837. */
  838.  
  839. %global Q;
  840. %let Q=Q;
  841. %let v=v;
  842. %global latestq;
  843. %let latestq=&v&end&Q&eq;
  844. %let firsty=&v&start;
  845.  
  846. /****************************/
  847. /*  Quarterly with Yearly   */
  848. /****************************/
  849.  
  850. proc sort data=&tmplib..Ystep1var_tr;
  851.   by Indicator;
  852. run;
  853.  
  854. proc sort data=&tmplib..Qstep1var_tr;
  855.   by Indicator;
  856. run;
  857.  
  858. /*******************/
  859. /* Join the tables */
  860. /*******************/
  861.  
  862. data &tmplib..YQstep1var_tr;
  863. merge &tmplib..Ystep1var_tr &tmplib..Qstep1var_tr;
  864. by Indicator;
  865. run;
  866.  
  867. proc sort Data=&tmplib..YQstep1var_tr;
  868.   by Indicator;
  869. run;
  870.  
  871. data &tmplib..YQstep1var_tr;
  872. set &tmplib..YQstep1var_tr;
  873. &variable="&val";
  874. run;
  875.  
  876. /* Put the chosen variable at the beginning os the dataset */
  877. data &tmplib..YQstep1var_tr;
  878. retain &variable;
  879. set &tmplib..YQstep1var_tr;
  880. run;
  881.  
  882. /********************/
  883. /*  Fancy sorting   */
  884. /********************/
  885.  
  886. data &tmplib..YQstep1var_tr;
  887. set &tmplib..YQstep1var_tr;
  888. if Indicator="IBC-1A" then sort1=1;
  889. if Indicator="ILL-3" then sort1=2;
  890. if Indicator="REF-4" then sort1=3;
  891. if Indicator="ASY-5" then sort1=4;
  892. if Indicator="RET-7A" then sort1=5;
  893. if Indicator="RET-7B" then sort1=6;
  894. if Indicator="FAL-6" then sort1=7;
  895. if Indicator="FAC-2" then sort1=8;
  896. if Indicator="IBC-1B" then sort1=9;
  897. run;
  898.  
  899. proc sort Data=&tmplib..YQstep1var_tr;
  900.   by sort1 descending &latestq;
  901. run;
  902.  
  903. data &tmplib..YQstep1var_tr;
  904. set &tmplib..YQstep1var_tr;
  905. drop sort1;
  906. run;
  907.  
  908. /*********************/
  909. /* Reporting_country */
  910. /*********************/
  911.  
  912. /* Choose 2nd dividing variable => ok */
  913. %let variable2=reporting_country_label;
  914. %let suffix=RC;
  915.  
  916. proc sort data=&tmplib..Ystep1var&suffix._tr;
  917.   by Indicator &variable2;
  918. run;
  919.  
  920. proc sort data=&tmplib..Qstep1var&suffix._tr;
  921.   by Indicator &variable2;
  922. run;
  923.  
  924. /*******************/
  925. /* Join the tables */
  926. /*******************/
  927.  
  928. data &tmplib..YQstep1var&suffix._tr;
  929. retain &variable;
  930. merge &tmplib..Ystep1var&suffix._tr &tmplib..Qstep1var&suffix._tr;
  931. by Indicator &variable2;
  932. run;
  933.  
  934. data &tmplib..YQshares_tr;
  935. retain &variable;
  936. merge &tmplib..Yshares_tr &tmplib..Qshares_tr;
  937. by Indicator;
  938. &variable="&val";
  939. run;
  940.  
  941. /****************************************/
  942. /* Sort by Indicator and latest quarter */
  943. /****************************************/
  944.  
  945. proc sort Data=&tmplib..YQstep1var&suffix._tr ;
  946. by Indicator descending &latestq;
  947. run;
  948.  
  949. /************************************/
  950. /*  Choose top n rows by indicators */
  951. /************************************/
  952.  
  953. %global n;
  954. %let n=⊤
  955.  
  956. data &tmplib..YQstep1var&suffix._tr_top;
  957. set &tmplib..YQstep1var&suffix._tr;
  958. count + 1;
  959. by Indicator;
  960. if first.Indicator then count = 1;
  961. run;
  962.  
  963. /* Top n rows */
  964. data &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_others;
  965. set &tmplib..YQstep1var&suffix._tr_top;
  966.     if count<=&n then output &tmplib..YQstep1var&suffix._tr_topn;
  967.     if count>&n then output &tmplib..YQstep1var&suffix._tr_others;
  968. run;
  969.  
  970. proc means data=&tmplib..YQstep1var&suffix._tr_others noprint;
  971. class Indicator;
  972. var &firsty--&latestq;
  973. output out=&tmplib..YQstep1var&suffix._tr_othersagg sum=;
  974. run;
  975.  
  976. data &tmplib..YQstep1var&suffix._tr_othersagg;
  977. set &tmplib..YQstep1var&suffix._tr_othersagg;
  978. where Indicator ne "";
  979. drop _TYPE_ _FREQ_;
  980. &variable="&val";
  981. &variable2="others";
  982. run;
  983.  
  984. data &tmplib..YQstep1var&suffix._tr_final;
  985. set &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_othersagg;
  986. run;
  987.  
  988. /*  Fancy sorting   */
  989. data &tmplib..YQstep1var&suffix._tr_final;
  990. set &tmplib..YQstep1var&suffix._tr_final;
  991. if Indicator="IBC-1A" then sort1=1;
  992. if Indicator="ILL-3" then sort1=2;
  993. if Indicator="REF-4" then sort1=3;
  994. if Indicator="ASY-5" then sort1=4;
  995. if Indicator="RET-7A" then sort1=5;
  996. if Indicator="RET-7B" then sort1=6;
  997. if Indicator="FAL-6" then sort1=7;
  998. if Indicator="FAC-2" then sort1=8;
  999. if Indicator="IBC-1B" then sort1=9;
  1000. if &variable2 ne "others" then sort2=1;
  1001. if &variable2 = "others" then sort2=2;
  1002. run;
  1003.  
  1004. proc sort Data=&tmplib..YQstep1var&suffix._tr_final;
  1005.     by sort1 sort2 descending &latestq;
  1006. run;
  1007.  
  1008. data &tmplib..YQstep1var&suffix._tr_final;
  1009. set &tmplib..YQstep1var&suffix._tr_final;
  1010. drop sort1 sort2 count;
  1011. run;
  1012.  
  1013. /************************/
  1014. /*  KeyBorderSection    */
  1015. /************************/
  1016.  
  1017. /* Choose 2nd dividing variable => ok */
  1018. %let variable2=KeyBorderSection;
  1019. %let suffix=KBS;
  1020.  
  1021. proc sort data=&tmplib..Ystep1var&suffix._tr;
  1022.   by Indicator &variable2;
  1023. run;
  1024.  
  1025. proc sort Data=&tmplib..Qstep1var&suffix._tr;
  1026.   by Indicator &variable2;
  1027. run;
  1028.  
  1029. /*******************/
  1030. /* Join the tables */
  1031. /*******************/
  1032.  
  1033. data &tmplib..YQstep1var&suffix._tr;
  1034. merge &tmplib..Ystep1var&suffix._tr &tmplib..Qstep1var&suffix._tr;
  1035. by Indicator &variable2;
  1036. drop _NAME_;
  1037. run;
  1038.  
  1039.  
  1040. /****************************************/
  1041. /* Sort by Indicator and latest quarter */
  1042. /****************************************/
  1043.  
  1044. proc sort Data=&tmplib..YQstep1var&suffix._tr;
  1045. by Indicator descending &latestq;
  1046. run;
  1047.  
  1048. /************************************/
  1049. /*  Choose top n rows by indicators */
  1050. /************************************/
  1051.  
  1052. %global n;
  1053. %let n=&top;
  1054.  
  1055. data &tmplib..YQstep1var&suffix._tr_top;
  1056. set &tmplib..YQstep1var&suffix._tr;
  1057. count + 1;
  1058. by Indicator;
  1059. if first.Indicator then count = 1;
  1060. run;
  1061.  
  1062. /* Top n rows */
  1063. data &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_others;
  1064. set &tmplib..YQstep1var&suffix._tr_top;
  1065.     if count<=&n then output &tmplib..YQstep1var&suffix._tr_topn;
  1066.     if count>&n then output &tmplib..YQstep1var&suffix._tr_others;
  1067. run;
  1068.  
  1069. /*
  1070. data &tmplib..YMstep1var&suffix._tr_topn;
  1071. set &tmplib..YMstep1var&suffix._tr_top;
  1072. where count<=&n;
  1073. drop count;
  1074. run;
  1075.  
  1076. /* rows to be aggregated as "others" *//*
  1077. data &tmplib..YMstep1var&suffix._tr_others;
  1078. set &tmplib..YMstep1var&suffix._tr_top;
  1079. where count>&n;
  1080. drop count;
  1081. run;*/
  1082.  
  1083. proc means data=&tmplib..YQstep1var&suffix._tr_others noprint;
  1084. class Indicator;
  1085. var &firsty--&latestq;
  1086. output out=&tmplib..YQstep1var&suffix._tr_othersagg sum=;
  1087. run;
  1088.  
  1089. data &tmplib..YQstep1var&suffix._tr_othersagg;
  1090. set &tmplib..YQstep1var&suffix._tr_othersagg;
  1091. where Indicator ne "";
  1092. drop _TYPE_ _FREQ_;
  1093. &variable="&val";
  1094. &variable2="others";
  1095. run;
  1096.  
  1097. data &tmplib..YQstep1var&suffix._tr_final;
  1098. set &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_othersagg;
  1099. drop count;
  1100. run;
  1101.  
  1102. /*  Fancy sorting   */
  1103. data &tmplib..YQstep1var&suffix._tr_final;
  1104. set &tmplib..YQstep1var&suffix._tr_final;
  1105. if Indicator="IBC-1A" then sort1=1;
  1106. if Indicator="ILL-3" then sort1=2;
  1107. if Indicator="REF-4" then sort1=3;
  1108. if Indicator="ASY-5" then sort1=4;
  1109. if Indicator="RET-7A" then sort1=5;
  1110. if Indicator="RET-7B" then sort1=6;
  1111. if Indicator="FAL-6" then sort1=7;
  1112. if Indicator="FAC-2" then sort1=8;
  1113. if Indicator="IBC-1B" then sort1=9;
  1114. if &variable2 ne "others" then sort2=1;
  1115. if &variable2 = "others" then sort2=2;
  1116. run;
  1117.  
  1118. proc sort Data=&tmplib..YQstep1var&suffix._tr_final;
  1119.   by sort1 sort2 descending &latestq;
  1120. run;
  1121.  
  1122. data &tmplib..YQstep1var&suffix._tr_final;
  1123. set &tmplib..YQstep1var&suffix._tr_final;
  1124. drop sort1 sort2;
  1125. run;
  1126.  
  1127. /******************/
  1128. /* BorderLocation */
  1129. /******************/
  1130.  
  1131. /* Choose 2nd dividing variable => ok */
  1132. %let variable2=BorderLocation;
  1133. %let suffix=BL;
  1134.  
  1135. proc sort data=&tmplib..Ystep1var&suffix._tr;
  1136.     by Indicator &variable2;
  1137. run;
  1138.  
  1139. /****************************/
  1140. /*  Quarterly with Yearly   */
  1141. /****************************/
  1142.  
  1143. proc sort Data=&tmplib..Qstep1var&suffix._tr;
  1144.     by Indicator &variable2;
  1145. run;
  1146.  
  1147. /*******************/
  1148. /* Join the tables */
  1149. /*******************/
  1150.  
  1151. data &tmplib..YQstep1var&suffix._tr;
  1152. merge &tmplib..Ystep1var&suffix._tr &tmplib..Qstep1var&suffix._tr;
  1153. by Indicator &variable2;
  1154. drop _NAME_;
  1155. run;
  1156.  
  1157. /****************************************/
  1158. /* Sort by Indicator and latest quarter */
  1159. /****************************************/
  1160.  
  1161. /********************/
  1162. /*  Fancy sorting   */
  1163. /********************/
  1164.  
  1165. data &tmplib..YQstep1var&suffix._tr;
  1166. set &tmplib..YQstep1var&suffix._tr;
  1167. if Indicator="IBC-1A" then sort1=1;
  1168. if Indicator="ILL-3" then sort1=2;
  1169. if Indicator="REF-4" then sort1=3;
  1170. if Indicator="ASY-5" then sort1=4;
  1171. if Indicator="RET-7A" then sort1=5;
  1172. if Indicator="RET-7B" then sort1=6;
  1173. if Indicator="FAL-6" then sort1=7;
  1174. if Indicator="FAC-2" then sort1=8;
  1175. if Indicator="IBC-1B" then sort1=9;
  1176. run;
  1177.  
  1178. proc sort Data=&tmplib..YQstep1var&suffix._tr;
  1179.   by sort1 descending &latestq;
  1180. run;
  1181.  
  1182. data &tmplib..YQstep1var&suffix._tr;
  1183. set &tmplib..YQstep1var&suffix._tr;
  1184. drop sort1;
  1185. run;
  1186.  
  1187. /********************************************/
  1188. /*  BorderLocation by top n MemberStates    */
  1189. /********************************************/
  1190.  
  1191. /*  Choose 3rd dividing variable => ok  */
  1192. %let variable3=reporting_country_label;
  1193. %let suffix2=RC;
  1194.  
  1195. proc sort data=&tmplib..Ystep1var&suffix._&suffix2._tr;
  1196.   by Indicator &variable2 &variable3;
  1197. run;
  1198.  
  1199. proc sort data=&tmplib..Qstep1var&suffix._&suffix2._tr;
  1200.   by Indicator &variable2 &variable3;
  1201. run;
  1202.  
  1203. /*******************/
  1204. /* Join the tables */
  1205. /*******************/
  1206.  
  1207. data &tmplib..YQstep1var&suffix._&suffix2._tr;
  1208. retain &variable;
  1209. merge &tmplib..Ystep1var&suffix._&suffix2._tr &tmplib..Qstep1var&suffix._&suffix2._tr;
  1210. by Indicator &variable2 &variable3;
  1211. run;
  1212.  
  1213. /****************************************/
  1214. /* Sort by Indicator and latest quarter */
  1215. /****************************************/
  1216.  
  1217. proc sort Data=&tmplib..YQstep1var&suffix._&suffix2._tr ;
  1218. by Indicator &variable2 descending &latestq;
  1219. run;
  1220.  
  1221. /************************************/
  1222. /*  Choose top n rows by indicators */
  1223. /************************************/
  1224.  
  1225. %global n;
  1226. %let n=&top;
  1227.  
  1228. data &tmplib..YQstep1var&suffix._&suffix2._tr_top;
  1229. set &tmplib..YQstep1var&suffix._&suffix2._tr;
  1230. count + 1;
  1231. by Indicator &variable2;
  1232. if first.Indicator or first.&variable2 then count = 1;
  1233. run;
  1234.  
  1235. /* Top n rows */
  1236. data &tmplib..YQstep1var&suffix._&suffix2._tr_topn(drop=count) &tmplib..YQstep1var&suffix._&suffix2._tr_others(drop=count);
  1237. set &tmplib..YQstep1var&suffix._&suffix2._tr_top(drop=_NAME_);
  1238.     if count<=&n then output &tmplib..YQstep1var&suffix._&suffix2._tr_topn;
  1239.     if count>&n then output &tmplib..YQstep1var&suffix._&suffix2._tr_others;
  1240. run;
  1241.  
  1242. proc means data=&tmplib..YQstep1var&suffix._&suffix2._tr_others noprint;
  1243. class Indicator &variable2;
  1244. var &firsty--&latestq;
  1245. output out=&tmplib..YQstep1var&suffix._&suffix2._tr_othersagg sum=;
  1246. run;
  1247.  
  1248. data &tmplib..YQstep1var&suffix._&suffix2._tr_othersagg;
  1249. set &tmplib..YQstep1var&suffix._&suffix2._tr_othersagg;
  1250. where Indicator ne "" and &variable2 ne "";
  1251. drop _TYPE_ _FREQ_;
  1252. &variable="&val";
  1253. &variable3="otherMS";
  1254. run;
  1255.  
  1256. data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1257. set &tmplib..YQstep1var&suffix._&suffix2._tr_topn &tmplib..YQstep1var&suffix._&suffix2._tr_othersagg;
  1258. run;
  1259.  
  1260. /*  Fancy sorting   */
  1261. data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1262. set &tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1263. if Indicator="IBC-1A" then sort1=1;
  1264. if Indicator="ILL-3" then sort1=2;
  1265. if Indicator="REF-4" then sort1=3;
  1266. if Indicator="ASY-5" then sort1=4;
  1267. if Indicator="RET-7A" then sort1=5;
  1268. if Indicator="RET-7B" then sort1=6;
  1269. if Indicator="FAL-6" then sort1=7;
  1270. if Indicator="FAC-2" then sort1=8;
  1271. if Indicator="IBC-1B" then sort1=9;
  1272. if &variable3 ne "otherMS" then sort2=1;
  1273. if &variable3 = "otherMS" then sort2=2;
  1274. run;
  1275.  
  1276. proc sort Data=&tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1277.     by sort1 &variable2 sort2 descending &latestq;
  1278. run;
  1279.  
  1280. data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1281. set &tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1282. drop sort1 sort2 count;
  1283. run;
  1284.  
  1285. /************************************************/
  1286. /*  Tables of years and quarters are ready!!!!  */
  1287. /************************************************/
  1288.  
  1289. /********************************/
  1290. /*                              */
  1291. /*      Monthly with Yearly     */
  1292. /*                              */
  1293. /********************************/
  1294.  
  1295. %lm;
  1296.  
  1297. proc sort data=&tmplib..Ystep1var_tr;
  1298.   by Indicator;
  1299. run;
  1300.  
  1301. proc sort data=&tmplib..Mstep1var_tr;
  1302.   by Indicator;
  1303. run;
  1304.  
  1305. /*******************/
  1306. /* Join the tables */
  1307. /*******************/
  1308.  
  1309. data &tmplib..YMstep1var_tr;
  1310. merge &tmplib..Ystep1var_tr &tmplib..Mstep1var_tr;
  1311. by Indicator;
  1312. run;
  1313.  
  1314. data &tmplib..YMstep1var_tr;
  1315. set &tmplib..YMstep1var_tr;
  1316. &variable="&val";
  1317. run;
  1318.  
  1319. /********************/
  1320. /*  Fancy sorting   */
  1321. /********************/
  1322.  
  1323. data &tmplib..YMstep1var_tr;
  1324. set &tmplib..YMstep1var_tr;
  1325. if Indicator="IBC-1A" then sort1=1;
  1326. if Indicator="ILL-3" then sort1=2;
  1327. if Indicator="REF-4" then sort1=3;
  1328. if Indicator="ASY-5" then sort1=4;
  1329. if Indicator="RET-7A" then sort1=5;
  1330. if Indicator="RET-7B" then sort1=6;
  1331. if Indicator="FAL-6" then sort1=7;
  1332. if Indicator="FAC-2" then sort1=8;
  1333. if Indicator="IBC-1B" then sort1=9;
  1334. run;
  1335.  
  1336. proc sort Data=&tmplib..YMstep1var_tr;
  1337.   by sort1 descending &latestm;
  1338. run;
  1339.  
  1340. data &tmplib..YMstep1var_tr;
  1341. set &tmplib..YMstep1var_tr;
  1342. drop sort1;
  1343. run;
  1344.  
  1345. /*********************/
  1346. /* Reporting_country */
  1347. /*********************/
  1348.  
  1349. /* Choose 2nd dividing variable => ok */
  1350. %let variable2=reporting_country_label;
  1351. %let suffix=RC;
  1352.  
  1353. proc sort data=&tmplib..Ystep1var&suffix._tr;
  1354.   by Indicator &variable2;
  1355. run;
  1356.  
  1357. proc sort Data=&tmplib..Mstep1var&suffix._tr;
  1358.   by Indicator &variable2;
  1359. run;
  1360.  
  1361. /*******************/
  1362. /* Join the tables */
  1363. /*******************/
  1364.  
  1365. data &tmplib..YMstep1var&suffix._tr;
  1366. merge &tmplib..Ystep1var&suffix._tr &tmplib..Mstep1var&suffix._tr;
  1367. by Indicator &variable2;
  1368. drop _NAME_;
  1369. run;
  1370.  
  1371. data &tmplib..YMshares_tr;
  1372. retain &variable;
  1373. merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
  1374. by Indicator;
  1375. run;
  1376.  
  1377. data &tmplib..YMshares_tr;
  1378. set &tmplib..YMshares_tr;
  1379. &variable="&val";
  1380. run;
  1381.  
  1382. /****************************************/
  1383. /* Sort by Indicator and latest month   */
  1384. /****************************************/
  1385.  
  1386. %lm;
  1387.  
  1388. proc sort Data=&tmplib..YMstep1var&suffix._tr;
  1389. by Indicator descending &latestm;
  1390. run;
  1391.  
  1392. /************************************/
  1393. /*  Choose top n rows by indicators */
  1394. /************************************/
  1395.  
  1396. %global n;
  1397. %let n=&top;
  1398.  
  1399. data &tmplib..YMstep1var&suffix._tr_top;
  1400. set &tmplib..YMstep1var&suffix._tr;
  1401. count + 1;
  1402. by Indicator;
  1403. if first.Indicator then count = 1;
  1404. run;
  1405.  
  1406. /* Top n rows */
  1407. data &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_others;
  1408. set &tmplib..YMstep1var&suffix._tr_top;
  1409.     if count<=&n then output &tmplib..YMstep1var&suffix._tr_topn;
  1410.     if count>&n then output &tmplib..YMstep1var&suffix._tr_others;
  1411. run;
  1412.  
  1413. /*
  1414. data &tmplib..YMstep1var&suffix._tr_topn;
  1415. set &tmplib..YMstep1var&suffix._tr_top;
  1416. where count<=&n;
  1417. drop count;
  1418. run;
  1419.  
  1420. /* rows to be aggregated as "others" *//*
  1421. data &tmplib..YMstep1var&suffix._tr_others;
  1422. set &tmplib..YMstep1var&suffix._tr_top;
  1423. where count>&n;
  1424. drop count;
  1425. run;*/
  1426.  
  1427. proc means data=&tmplib..YMstep1var&suffix._tr_others noprint;
  1428. class Indicator;
  1429. var &firsty--&latestm;
  1430. output out=&tmplib..YMstep1var&suffix._tr_othersagg sum=;
  1431. run;
  1432.  
  1433. data &tmplib..YMstep1var&suffix._tr_othersagg;
  1434. set &tmplib..YMstep1var&suffix._tr_othersagg;
  1435. where Indicator ne "";
  1436. drop _TYPE_ _FREQ_;
  1437. &variable="&val";
  1438. &variable2="others";
  1439. run;
  1440.  
  1441. data &tmplib..YMstep1var&suffix._tr_final;
  1442. set &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_othersagg;
  1443. drop count;
  1444. run;
  1445.  
  1446. /*  Fancy sorting   */
  1447. data &tmplib..YMstep1var&suffix._tr_final;
  1448. set &tmplib..YMstep1var&suffix._tr_final;
  1449. if Indicator="IBC-1A" then sort1=1;
  1450. if Indicator="ILL-3" then sort1=2;
  1451. if Indicator="REF-4" then sort1=3;
  1452. if Indicator="ASY-5" then sort1=4;
  1453. if Indicator="RET-7A" then sort1=5;
  1454. if Indicator="RET-7B" then sort1=6;
  1455. if Indicator="FAL-6" then sort1=7;
  1456. if Indicator="FAC-2" then sort1=8;
  1457. if Indicator="IBC-1B" then sort1=9;
  1458. if &variable2 ne "others" then sort2=1;
  1459. if &variable2 = "others" then sort2=2;
  1460. run;
  1461.  
  1462. proc sort Data=&tmplib..YMstep1var&suffix._tr_final;
  1463.   by sort1 sort2 descending &latestm;
  1464. run;
  1465.  
  1466. data &tmplib..YMstep1var&suffix._tr_final;
  1467. set &tmplib..YMstep1var&suffix._tr_final;
  1468. drop sort1 sort2;
  1469. run;
  1470.  
  1471. /************************/
  1472. /*  KeyBorderSection    */
  1473. /************************/
  1474.  
  1475. /* Choose 2nd dividing variable => ok */
  1476. %let variable2=KeyBorderSection;
  1477. %let suffix=KBS;
  1478.  
  1479. proc sort data=&tmplib..Ystep1var&suffix._tr;
  1480.   by Indicator &variable2;
  1481. run;
  1482.  
  1483. proc sort Data=&tmplib..Mstep1var&suffix._tr;
  1484.   by Indicator &variable2;
  1485. run;
  1486.  
  1487. /*******************/
  1488. /* Join the tables */
  1489. /*******************/
  1490.  
  1491. data &tmplib..YMstep1var&suffix._tr;
  1492. merge &tmplib..Ystep1var&suffix._tr &tmplib..Mstep1var&suffix._tr;
  1493. by Indicator &variable2;
  1494. drop _NAME_;
  1495. run;
  1496.  
  1497. data &tmplib..YMshares_tr;
  1498. retain &variable;
  1499. merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
  1500. by Indicator;
  1501. run;
  1502.  
  1503. data &tmplib..YMshares_tr;
  1504. set &tmplib..YMshares_tr;
  1505. &variable="&val";
  1506. run;
  1507.  
  1508. /****************************************/
  1509. /* Sort by Indicator and latest month   */
  1510. /****************************************/
  1511.  
  1512. proc sort Data=&tmplib..YMstep1var&suffix._tr;
  1513. by Indicator descending &latestm;
  1514. run;
  1515.  
  1516. /************************************/
  1517. /*  Choose top n rows by indicators */
  1518. /************************************/
  1519.  
  1520. %global n;
  1521. %let n=&top;
  1522.  
  1523. data &tmplib..YMstep1var&suffix._tr_top;
  1524. set &tmplib..YMstep1var&suffix._tr;
  1525. count + 1;
  1526. by Indicator;
  1527. if first.Indicator then count = 1;
  1528. run;
  1529.  
  1530. /* Top n rows */
  1531. data &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_others;
  1532. set &tmplib..YMstep1var&suffix._tr_top;
  1533.     if count<=&n then output &tmplib..YMstep1var&suffix._tr_topn;
  1534.     if count>&n then output &tmplib..YMstep1var&suffix._tr_others;
  1535. run;
  1536.  
  1537. /*
  1538. data &tmplib..YMstep1var&suffix._tr_topn;
  1539. set &tmplib..YMstep1var&suffix._tr_top;
  1540. where count<=&n;
  1541. drop count;
  1542. run;
  1543.  
  1544. /* rows to be aggregated as "others" *//*
  1545. data &tmplib..YMstep1var&suffix._tr_others;
  1546. set &tmplib..YMstep1var&suffix._tr_top;
  1547. where count>&n;
  1548. drop count;
  1549. run;*/
  1550.  
  1551. proc means data=&tmplib..YMstep1var&suffix._tr_others noprint;
  1552. class Indicator;
  1553. var &firsty--&latestm;
  1554. output out=&tmplib..YMstep1var&suffix._tr_othersagg sum=;
  1555. run;
  1556.  
  1557. data &tmplib..YMstep1var&suffix._tr_othersagg;
  1558. set &tmplib..YMstep1var&suffix._tr_othersagg;
  1559. where Indicator ne "";
  1560. drop _TYPE_ _FREQ_;
  1561. &variable="&val";
  1562. &variable2="others";
  1563. run;
  1564.  
  1565. data &tmplib..YMstep1var&suffix._tr_final;
  1566. set &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_othersagg;
  1567. drop count;
  1568. run;
  1569.  
  1570. /*  Fancy sorting   */
  1571. data &tmplib..YMstep1var&suffix._tr_final;
  1572. set &tmplib..YMstep1var&suffix._tr_final;
  1573. if Indicator="IBC-1A" then sort1=1;
  1574. if Indicator="ILL-3" then sort1=2;
  1575. if Indicator="REF-4" then sort1=3;
  1576. if Indicator="ASY-5" then sort1=4;
  1577. if Indicator="RET-7A" then sort1=5;
  1578. if Indicator="RET-7B" then sort1=6;
  1579. if Indicator="FAL-6" then sort1=7;
  1580. if Indicator="FAC-2" then sort1=8;
  1581. if Indicator="IBC-1B" then sort1=9;
  1582. if &variable2 ne "others" then sort2=1;
  1583. if &variable2 = "others" then sort2=2;
  1584. run;
  1585.  
  1586. proc sort Data=&tmplib..YMstep1var&suffix._tr_final;
  1587.   by sort1 sort2 descending &latestm;
  1588. run;
  1589.  
  1590. data &tmplib..YMstep1var&suffix._tr_final;
  1591. set &tmplib..YMstep1var&suffix._tr_final;
  1592. drop sort1 sort2;
  1593. run;
  1594.  
  1595. /******************/
  1596. /* BorderLocation */
  1597. /******************/
  1598.  
  1599. /* Choose 2nd dividing variable => ok */
  1600. %let variable2=BorderLocation;
  1601. %let suffix=BL;
  1602.  
  1603. proc sort data=&tmplib..Ystep1var&suffix._tr;
  1604.   by Indicator &variable2;
  1605. run;
  1606.  
  1607. /********************************/
  1608. /*      Monthly with Yearly     */
  1609. /********************************/
  1610.  
  1611. proc sort Data=&tmplib..Mstep1var&suffix._tr;
  1612.   by Indicator &variable2;
  1613. run;
  1614.  
  1615. /*******************/
  1616. /* Join the tables */
  1617. /*******************/
  1618.  
  1619. data &tmplib..YMstep1var&suffix._tr;
  1620. merge &tmplib..Ystep1var&suffix._tr &tmplib..Mstep1var&suffix._tr;
  1621. by Indicator &variable2;
  1622. drop _NAME_;
  1623. run;
  1624.  
  1625. data &tmplib..YMshares_tr;
  1626. retain &variable;
  1627. merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
  1628. by Indicator;
  1629. run;
  1630.  
  1631. /****************************************/
  1632. /* Sort by Indicator and latest month   */
  1633. /****************************************/
  1634.  
  1635. proc sort Data=&tmplib..YMstep1var&suffix._tr;
  1636. by Indicator descending &latestm;
  1637. run;
  1638.  
  1639. /********************************/
  1640. /*  Border location - no top 5  */
  1641. /********************************/
  1642.  
  1643. /*  Fancy sorting   */
  1644. data &tmplib..YMstep1var&suffix._tr;
  1645. set &tmplib..YMstep1var&suffix._tr;
  1646. if Indicator="IBC-1A" then sort1=1;
  1647. if Indicator="ILL-3" then sort1=2;
  1648. if Indicator="REF-4" then sort1=3;
  1649. if Indicator="ASY-5" then sort1=4;
  1650. if Indicator="RET-7A" then sort1=5;
  1651. if Indicator="RET-7B" then sort1=6;
  1652. if Indicator="FAL-6" then sort1=7;
  1653. if Indicator="FAC-2" then sort1=8;
  1654. if Indicator="IBC-1B" then sort1=9;
  1655. run;
  1656.  
  1657. proc sort Data=&tmplib..YMstep1var&suffix._tr;
  1658.   by sort1 descending &latestm;
  1659. run;
  1660.  
  1661. data &tmplib..YMstep1var&suffix._tr;
  1662. retain &variable &variable2;
  1663. set &tmplib..YMstep1var&suffix._tr;
  1664. drop sort1;
  1665. run;
  1666.  
  1667. /********************************************/
  1668. /*  BorderLocation by top n MemberStates    */
  1669. /********************************************/
  1670.  
  1671. %let variable3=reporting_country_label;
  1672. %let suffix2=RC;
  1673.  
  1674. proc sort data=&tmplib..Ystep1var&suffix._&suffix2._tr;
  1675.   by Indicator &variable2 &variable3;
  1676. run;
  1677.  
  1678. proc sort data=&tmplib..Mstep1var&suffix._&suffix2._tr;
  1679.   by Indicator &variable2 &variable3;
  1680. run;
  1681.  
  1682. /*******************/
  1683. /* Join the tables */
  1684. /*******************/
  1685.  
  1686. data &tmplib..YMstep1var&suffix._&suffix2._tr;
  1687. retain &variable;
  1688. merge &tmplib..Ystep1var&suffix._&suffix2._tr &tmplib..Mstep1var&suffix._&suffix2._tr;
  1689. by Indicator &variable2 &variable3;
  1690. run;
  1691.  
  1692. /****************************************/
  1693. /* Sort by Indicator and latest quarter */
  1694. /****************************************/
  1695.  
  1696. proc sort Data=&tmplib..YMstep1var&suffix._&suffix2._tr ;
  1697. by Indicator &variable2 descending &latestm;
  1698. run;
  1699.  
  1700. /************************************/
  1701. /*  Choose top n rows by indicators */
  1702. /************************************/
  1703.  
  1704. %global n;
  1705. %let n=&top;
  1706.  
  1707. data &tmplib..YMstep1var&suffix._&suffix2._tr_top;
  1708. set &tmplib..YMstep1var&suffix._&suffix2._tr;
  1709. count + 1;
  1710. by Indicator &variable2;
  1711. if first.Indicator or first.&variable2 then count = 1;
  1712. run;
  1713.  
  1714. /* Top n rows */
  1715. data &tmplib..YMstep1var&suffix._&suffix2._tr_topn(drop=count) &tmplib..YMstep1var&suffix._&suffix2._tr_others(drop=count);
  1716. set &tmplib..YMstep1var&suffix._&suffix2._tr_top(drop=_NAME_);
  1717.     if count<=&n then output &tmplib..YMstep1var&suffix._&suffix2._tr_topn;
  1718.     if count>&n then output &tmplib..YMstep1var&suffix._&suffix2._tr_others;
  1719. run;
  1720.  
  1721. proc means data=&tmplib..YMstep1var&suffix._&suffix2._tr_others noprint;
  1722. class Indicator &variable2;
  1723. var &firsty--&latestm;
  1724. output out=&tmplib..YMstep1var&suffix._&suffix2._tr_othersagg sum=;
  1725. run;
  1726.  
  1727. data &tmplib..YMstep1var&suffix._&suffix2._tr_othersagg;
  1728. set &tmplib..YMstep1var&suffix._&suffix2._tr_othersagg;
  1729. where Indicator ne "" and &variable2 ne "";
  1730. drop _TYPE_ _FREQ_;
  1731. &variable="&val";
  1732. &variable3="other MS";
  1733. run;
  1734.  
  1735. data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1736. set &tmplib..YMstep1var&suffix._&suffix2._tr_topn &tmplib..YMstep1var&suffix._&suffix2._tr_othersagg;
  1737. run;
  1738.  
  1739. /*  Fancy sorting   */
  1740. data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1741. set &tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1742. if Indicator="IBC-1A" then sort1=1;
  1743. if Indicator="ILL-3" then sort1=2;
  1744. if Indicator="REF-4" then sort1=3;
  1745. if Indicator="ASY-5" then sort1=4;
  1746. if Indicator="RET-7A" then sort1=5;
  1747. if Indicator="RET-7B" then sort1=6;
  1748. if Indicator="FAL-6" then sort1=7;
  1749. if Indicator="FAC-2" then sort1=8;
  1750. if Indicator="IBC-1B" then sort1=9;
  1751. if &variable3 ne "other MS" then sort2=1;
  1752. if &variable3 = "other MS" then sort2=2;
  1753. run;
  1754.  
  1755. proc sort Data=&tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1756.     by sort1 &variable2 sort2 descending &latestm;
  1757. run;
  1758.  
  1759. data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1760. set &tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1761. drop sort1 sort2 count;
  1762. run;
  1763.  
  1764. /************************************************/
  1765. /*  Tables of years and months are ready!!!!    */
  1766. /************************************************/
  1767.  
  1768. /************************************************************/
  1769. /*      Changing order of variables                         */
  1770. /*                                                          */
  1771. /*          BL - Border Location                            */
  1772. /*          BL_RC - Border Location by Reporting Country    */
  1773. /*          RC - Reporting Country                          */
  1774. /*          KBS - KeyBorderSection                          */
  1775. /*                                                          */
  1776. /*      Yearly with Quarterly                               */
  1777. /*      Yearly with Monthly                                 */
  1778. /*                                                          */
  1779. /************************************************************/
  1780.  
  1781. /*
  1782. &tmplib..YQstep1var_tr - Indicator
  1783. &tmplib..YQshares_tr - Indicator
  1784.  
  1785. &tmplib..YQstep1varRC_tr_final - Indicator, variable2
  1786. &tmplib..YQstep1varBL_tr_final - Indicator, variable2
  1787. &tmplib..YQstep1varBL_RC_tr_final - Indicator, variable2, variable3
  1788. &tmplib..YQstep1varKBS_tr_final - Indicator, variable2
  1789.  
  1790. &tmplib..YMstep1var_tr - Indicator
  1791. &tmplib..YMshares_tr - Indicator
  1792.  
  1793. &tmplib..YMstep1varRC_tr_final - Indicator, variable2
  1794. &tmplib..YMstep1varBL_tr_final  - Indicator, variable2
  1795. &tmplib..YMstep1varBL_RC_tr_final - Indicator, variable2, variable3
  1796. &tmplib..YMstep1varKBS_tr_final - Indicator, variable2
  1797.  
  1798. %let variable3=reporting_country_label;
  1799. %let suffix2=RC;
  1800.  
  1801. %global start;
  1802. %let start=2012;
  1803. %global end;
  1804. %let end=2013;
  1805.  
  1806. %global bq;
  1807. %let bq=2;
  1808. %global eq;
  1809. %let eq=3;
  1810.  
  1811. %global bm;
  1812. %let bm=1;
  1813. %global em;
  1814. %let em=8;
  1815. %global M;
  1816. %let M=M;
  1817.  
  1818. 1 macro for quarters
  1819. 1 macro for months
  1820.  
  1821. data &tmplib..YQstep1var_tr;
  1822. retain Indicator bla v2012Q2 v2012Q3 v2012Q4 v2012 v2013Q1 v2013Q2 v2013Q3 v2013;
  1823. set &tmplib..YQstep1var_tr;
  1824. run;
  1825. =>we can use variables not existing in a dataset.... =>GOOD ;-D
  1826. */
  1827.  
  1828. /********************************/
  1829. /*  Changing order of variables */
  1830. /********************************/
  1831.  
  1832. /* * * * * * * * * * */
  1833. /*  Quarterly data   */
  1834. /* * * * * * * * * * */
  1835.  
  1836. %qtr_order;
  1837. *%put &qorder.;
  1838.  
  1839. data &tmplib..YQstep1var_tr;
  1840. retain &variable Indicator &qorder;
  1841. set &tmplib..YQstep1var_tr;
  1842. run;
  1843.  
  1844. data &tmplib..YQshares_tr;
  1845. retain &variable Indicator &qorder;
  1846. set &tmplib..YQshares_tr;
  1847. run;
  1848.  
  1849. /********************/
  1850. /*  Fancy sorting   */
  1851. /********************/
  1852. data &tmplib..YQshares_tr;
  1853. set &tmplib..YQshares_tr;
  1854. if Indicator="IBC-1A" then sort1=1;
  1855. if Indicator="ILL-3" then sort1=2;
  1856. if Indicator="REF-4" then sort1=3;
  1857. if Indicator="ASY-5" then sort1=4;
  1858. if Indicator="RET-7A" then sort1=5;
  1859. if Indicator="RET-7B" then sort1=6;
  1860. if Indicator="FAL-6" then sort1=7;
  1861. if Indicator="FAC-2" then sort1=8;
  1862. if Indicator="IBC-1B" then sort1=9;
  1863. run;
  1864.  
  1865. proc sort data=&tmplib..YQshares_tr;
  1866.     by sort1;
  1867. run;
  1868.  
  1869. data &tmplib..YQshares_tr;
  1870. set &tmplib..YQshares_tr;
  1871. drop sort1;
  1872. run;
  1873.  
  1874. /* Choose 2nd dividing variable => ok */
  1875. %let variable2=reporting_country_label;
  1876. %let suffix=RC;
  1877.  
  1878. data &tmplib..YQstep1var&suffix._tr_final;
  1879. retain &variable Indicator &variable2 &qorder;
  1880. set &tmplib..YQstep1var&suffix._tr_final;
  1881. run;
  1882.  
  1883. /* Choose 2nd dividing variable => ok */
  1884. %let variable2=BorderLocation;
  1885. %let suffix=BL;
  1886.  
  1887. data &tmplib..YQstep1var&suffix._tr_Final;
  1888. retain &variable Indicator &variable2 &qorder;
  1889. set &tmplib..YQstep1var&suffix._tr_Final;
  1890. run;
  1891.  
  1892. /*  Choose 3rd dividing variable => ok  */
  1893. %let variable3=reporting_country_label;
  1894. %let suffix2=RC;
  1895.  
  1896. data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1897. retain &variable Indicator &variable2 &variable3 &qorder;
  1898. set &tmplib..YQstep1var&suffix._&suffix2._tr_final;
  1899. run;
  1900.  
  1901. /* Choose 2nd dividing variable => ok */
  1902. %let variable2=KeyBorderSection;
  1903. %let suffix=KBS;
  1904.  
  1905. data &tmplib..YQstep1var&suffix._tr_final;
  1906. retain &variable Indicator &variable2 &qorder;
  1907. set &tmplib..YQstep1var&suffix._tr_final;
  1908. run;
  1909.  
  1910. /* * * * * * * * * * */
  1911. /*   Monthly data    */
  1912. /* * * * * * * * * * */
  1913.  
  1914. %mth_order;
  1915. *%put &morder.;
  1916.  
  1917. data &tmplib..YMstep1var_tr;
  1918. retain &variable Indicator &morder;
  1919. set &tmplib..YMstep1var_tr;
  1920. run;
  1921.  
  1922. data &tmplib..YMshares_tr;
  1923. merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
  1924. by Indicator;
  1925. &variable="&val";
  1926. run;
  1927.  
  1928. data &tmplib..YMshares_tr;
  1929. retain &variable Indicator &morder;
  1930. set &tmplib..YMshares_tr;
  1931. run;
  1932.  
  1933. /********************/
  1934. /*  Fancy sorting   */
  1935. /********************/
  1936. data &tmplib..YMshares_tr;
  1937. set &tmplib..YMshares_tr;
  1938. if Indicator="IBC-1A" then sort1=1;
  1939. if Indicator="ILL-3" then sort1=2;
  1940. if Indicator="REF-4" then sort1=3;
  1941. if Indicator="ASY-5" then sort1=4;
  1942. if Indicator="RET-7A" then sort1=5;
  1943. if Indicator="RET-7B" then sort1=6;
  1944. if Indicator="FAL-6" then sort1=7;
  1945. if Indicator="FAC-2" then sort1=8;
  1946. if Indicator="IBC-1B" then sort1=9;
  1947. run;
  1948.  
  1949. proc sort data=&tmplib..YMshares_tr;
  1950.     by sort1;
  1951. run;
  1952.  
  1953. data &tmplib..YMshares_tr;
  1954. set &tmplib..YMshares_tr;
  1955. drop sort1;
  1956. run;
  1957.  
  1958. /* Choose 2nd dividing variable => ok */
  1959. %let variable2=reporting_country_label;
  1960. %let suffix=RC;
  1961.  
  1962. data &tmplib..YMstep1var&suffix._tr_final;
  1963. retain &variable Indicator &variable2 &morder;
  1964. set &tmplib..YMstep1var&suffix._tr_final;
  1965. drop count;
  1966. run;
  1967.  
  1968. /* Choose 2nd dividing variable => ok */
  1969. %let variable2=BorderLocation;
  1970. %let suffix=BL;
  1971.  
  1972. data &tmplib..YMstep1var&suffix._tr_final;
  1973. retain &variable Indicator &variable2 &morder;
  1974. set &tmplib..YMstep1var&suffix._tr;
  1975. run;
  1976.  
  1977. /*  Choose 3rd dividing variable => ok  */
  1978. %let variable3=reporting_country_label;
  1979. %let suffix2=RC;
  1980.  
  1981. data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1982. retain &variable Indicator &variable2 &variable3 &morder;
  1983. set &tmplib..YMstep1var&suffix._&suffix2._tr_final;
  1984. run;
  1985.  
  1986. /* Choose 2nd dividing variable => ok */
  1987. %let variable2=KeyBorderSection;
  1988. %let suffix=KBS;
  1989.  
  1990. data &tmplib..YMstep1var&suffix._tr_final;
  1991. retain &variable Indicator &variable2 &morder;
  1992. set &tmplib..YMstep1var&suffix._tr_final;
  1993. drop count;
  1994. run;
  1995.  
  1996. /*********************************/
  1997. /*Member States by BorderLocation*/
  1998. /*********************************/
  1999.  
  2000. %let variable=reporting_country_label;
  2001. %let ind="IBC-1A";
  2002.  
  2003. /*Yearly*/
  2004. proc sql noprint;
  2005.     create table &tmplib..Ystep3var as
  2006.     select distinct &variable, YEAR,Indicator, sum(Total) as sum_Total
  2007.     from &inlib..&set2.
  2008.     where YEAR in (&yrs) and Indicator=&ind.
  2009.     group by &variable, YEAR, Indicator
  2010.     order by &variable, Indicator, YEAR;
  2011. quit;
  2012.  
  2013. proc transpose data=&tmplib..Ystep3var  prefix=v
  2014.     out=&tmplib..Ystep3var_tr;
  2015.     by &variable Indicator;
  2016.     id YEAR;
  2017.     var sum_Total;
  2018. run;
  2019.  
  2020. /*Monthly*/
  2021. proc sql noprint;
  2022.     create table &tmplib..Mstep3var as
  2023.     select distinct &variable, YYYYMM,Indicator, sum(Total) as sum_Total
  2024.     from &inlib..&set2.
  2025.     where YYYYMM in (&mths) and Indicator=&ind.
  2026.     group by &variable, YYYYMM, Indicator
  2027.     order by &variable, Indicator, YYYYMM;
  2028. quit;
  2029.  
  2030. data &tmplib..Mstep3var;
  2031. set &tmplib..Mstep3var;
  2032.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  2033.     if sum_Total=. then sum_Total=0;
  2034. run;
  2035.  
  2036. proc transpose data=&tmplib..Mstep3var  prefix=v
  2037.     out=&tmplib..Mstep3var_tr;
  2038.     by &variable Indicator;
  2039.     id YYYYMM;
  2040.     var sum_Total;
  2041. run;
  2042.  
  2043. /*Merge*/
  2044. proc sort data=&tmplib..Ystep3var_tr;
  2045. by &variable;
  2046. run;
  2047.  
  2048. proc sort data=&tmplib..Mstep3var_tr;
  2049. by &variable;
  2050. run;
  2051.  
  2052. data &tmplib..YMstep3var_tr;
  2053. retain &variable Indicator &morder;
  2054. merge &tmplib..Ystep3var_tr &tmplib..Mstep3var_tr;
  2055. by &variable;
  2056. run;
  2057.  
  2058. proc sort data=&tmplib..YMstep3var_tr;
  2059. by descending &latestm;
  2060. run;
  2061.  
  2062.  
  2063. /********************************************************************************/
  2064. /*  3 tables -          1     2    3                                            */
  2065. /*      - border type (all, land, sea)                                          */
  2066. /*      - top 10 nationalities + others (aggregated)                            */
  2067. /*      - Totals by border types: all borders, land borders, sea borders etc    */
  2068. /*  Each - monthly with yearly                                                  */
  2069. /********************************************************************************/
  2070.  
  2071. %let variable=Nat_pers_label;
  2072. %let ind="IBC-1A";
  2073.  
  2074. /*  All borders */
  2075. /*Yearly*/
  2076. proc sql noprint;
  2077.     create table &tmplib..Ystep4var as
  2078.     select distinct &variable, YEAR,sum(Total) as sum_Total
  2079.     from &inlib..&set2.
  2080.     where YEAR in (&yrs) and Indicator in (&ind.)
  2081.     group by &variable, YEAR
  2082.     order by &variable, YEAR;
  2083. quit;
  2084.  
  2085. /* Reshape the dataset so that it looks the way we need.... */
  2086. proc transpose data=&tmplib..Ystep4var  prefix=v
  2087.     out=&tmplib..Ystep4var_tr;
  2088.     by &variable;
  2089.     id YEAR;
  2090.     var sum_Total;
  2091. run;
  2092.  
  2093. /*  Monthly */
  2094. proc sql noprint;
  2095.     create table &tmplib..Mstep4var as
  2096.     select distinct &variable, YYYYMM,sum(Total) as sum_Total
  2097.     from &inlib..&set2.
  2098.     where YYYYMM in (&mths) and Indicator in (&ind.)
  2099.     group by &variable, YYYYMM
  2100.     order by &variable, YYYYMM;
  2101. quit;
  2102.  
  2103. data &tmplib..Mstep4var;
  2104. set &tmplib..Mstep4var;
  2105.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  2106. run;
  2107.  
  2108. /* Reshape the dataset so that it looks the way we need.... */
  2109. proc transpose data=&tmplib..Mstep4var  prefix=v
  2110.     out=&tmplib..Mstep4var_tr;
  2111.     by &variable;
  2112.     id YYYYMM;
  2113.     var sum_Total;
  2114. run;
  2115.  
  2116. /*  Merge yearly with monthly   */
  2117. data &tmplib..YMstep4var_tr;
  2118. drop _NAME_;
  2119. merge &tmplib..Ystep4var_tr &tmplib..Mstep4var_tr;
  2120. by &variable;
  2121. run;
  2122.  
  2123. /*  Sort by latest month, then aggregate    */
  2124.  
  2125. proc sort Data=&tmplib..YMstep4var_tr;
  2126. by descending &latestm;
  2127. run;
  2128.  
  2129. /*  Top n=10    */
  2130. %let n=10;
  2131.  
  2132. /*  Number rows in sorted dataset   */
  2133. data &tmplib..YMstep4var_tr;
  2134. set &tmplib..YMstep4var_tr;
  2135. count=_N_;
  2136. run;
  2137.  
  2138. /* Split top n rows */
  2139. data &tmplib..YMstep4var_tr_topn &tmplib..YMstep4var_tr_others;
  2140. set &tmplib..YMstep4var_tr;
  2141.     if count<=&n then output &tmplib..YMstep4var_tr_topn;
  2142.     if count>&n then output &tmplib..YMstep4var_tr_others;
  2143. run;
  2144.  
  2145. proc means data=&tmplib..YMstep4var_tr_others noprint;
  2146. var &firsty--&latestm;
  2147. output out=&tmplib..YMstep4var_tr_othersagg sum=;
  2148. run;
  2149.  
  2150. data &tmplib..YMstep4var_tr_othersagg;
  2151. set &tmplib..YMstep4var_tr_othersagg;
  2152. drop _TYPE_ _FREQ_;
  2153. &variable="others";
  2154. run;
  2155.  
  2156. data &tmplib..YMstep4var_tr_final;
  2157. retain &variable &morder;
  2158. set &tmplib..YMstep4var_tr_topn &tmplib..YMstep4var_tr_othersagg;
  2159. drop count;
  2160. run;
  2161.  
  2162. /********************************************/
  2163. /*  By type of border - land, sea, air etc. */
  2164. /* * * * * * * * * * * * * * * * * * * * * * /
  2165. /*  Choose 2nd dividing variable => ok      */
  2166. /********************************************/
  2167. %let variable2=BorderLocation;
  2168. %let suffix=BL;
  2169.  
  2170. /*Yearly*/
  2171. proc sql noprint;
  2172.     create table &tmplib..Ystep4var&suffix. as
  2173.     select distinct &variable, &variable2, YEAR,sum(Total) as sum_Total
  2174.     from &inlib..&set2.
  2175.     where YEAR in (&yrs) and Indicator in (&ind.)
  2176.     group by &variable, &variable2, YEAR
  2177.     order by &variable, &variable2, YEAR;
  2178. quit;
  2179.  
  2180. /* Reshape the dataset so that it looks the way we need.... */
  2181. proc transpose data=&tmplib..Ystep4var&suffix.  prefix=v
  2182.     out=&tmplib..Ystep4var&suffix._tr;
  2183.     by &variable &variable2;
  2184.     id YEAR;
  2185.     var sum_Total;
  2186. run;
  2187.  
  2188. /*  Monthly */
  2189. proc sql noprint;
  2190.     create table &tmplib..Mstep4var&suffix. as
  2191.     select distinct &variable, &variable2, YYYYMM,sum(Total) as sum_Total
  2192.     from &inlib..&set2.
  2193.     where YYYYMM in (&mths) and Indicator in (&ind.)
  2194.     group by &variable, &variable2, YYYYMM
  2195.     order by &variable, &variable2, YYYYMM;
  2196. quit;
  2197.  
  2198. data &tmplib..Mstep4var&suffix.;
  2199. set &tmplib..Mstep4var&suffix.;
  2200.     YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
  2201. run;
  2202.  
  2203. /* Reshape the dataset so that it looks the way we need.... */
  2204. proc transpose data=&tmplib..Mstep4var&suffix.  prefix=v
  2205.     out=&tmplib..Mstep4var&suffix._tr;
  2206.     by &variable &variable2;
  2207.     id YYYYMM;
  2208.     var sum_Total;
  2209. run;
  2210.  
  2211. /*  Merge yearly with monthly   */
  2212. data &tmplib..YMstep4var&suffix._tr;
  2213. retain &variable &variable2;
  2214. drop _NAME_;
  2215. merge &tmplib..Ystep4var&suffix._tr &tmplib..Mstep4var&suffix._tr;
  2216. by &variable &variable2;
  2217. run;
  2218.  
  2219. /*  Sort by latest month, then aggregate    */
  2220.  
  2221. proc sort Data=&tmplib..YMstep4var&suffix._tr;
  2222. by &variable2 descending &latestm;
  2223. run;
  2224.  
  2225. /*  Top n=10    */
  2226. %let n=10;
  2227.  
  2228. data &tmplib..YMstep4var&suffix._tr_top;
  2229. set &tmplib..YMstep4var&suffix._tr;
  2230. count + 1;
  2231. by &variable2;
  2232. if first.&variable2. then count = 1;
  2233. run;
  2234.  
  2235. /* Top n rows */
  2236. data &tmplib..YMstep4var&suffix._tr_topn &tmplib..YMstep4var&suffix._tr_others;
  2237. set &tmplib..YMstep4var&suffix._tr_top;
  2238.     if count<=&n then output &tmplib..YMstep4var&suffix._tr_topn;
  2239.     if count>&n then output &tmplib..YMstep4var&suffix._tr_others;
  2240. run;
  2241.  
  2242. proc means data=&tmplib..YMstep4var&suffix._tr_others noprint;
  2243. class &variable2;
  2244. var &firsty--&latestm;
  2245. output out=&tmplib..YMstep4var&suffix._tr_othersagg sum=;
  2246. run;
  2247.  
  2248. data &tmplib..YMstep4var&suffix._tr_othersagg;
  2249. set &tmplib..YMstep4var&suffix._tr_othersagg;
  2250. drop _TYPE_ _FREQ_;
  2251. &variable="others";
  2252. run;
  2253.  
  2254. data &tmplib..YMstep4var&suffix._tr_final;
  2255. retain &variable &variable2 &morder;
  2256. set &tmplib..YMstep4var&suffix._tr_topn &tmplib..YMstep4var&suffix._tr_othersagg;
  2257. where &variable2 ne "" and &variable2 ne "Not specified";
  2258. run;
  2259.  
  2260. /********************/
  2261. /*  Fancy sorting   */
  2262. /********************/
  2263. data &tmplib..YMstep4var&suffix._tr_final;
  2264. set &tmplib..YMstep4var&suffix._tr_final;
  2265. if &variable ne "others" then sort2=1;
  2266. if &variable = "others" then sort2=2;
  2267. drop count;
  2268. run;
  2269.  
  2270. proc sort data=&tmplib..YMstep4var&suffix._tr_final out=&tmplib..YMstep4var&suffix._tr_final(drop=sort2);
  2271. by &variable2 sort2 descending &latestm;
  2272. run;
  2273.  
  2274. /********************************************************/
  2275. /*  Replace missing values with 0 - final tables only   */
  2276. /********************************************************/
  2277.  
  2278. data &tmplib..YQstep1var_tr(drop=i);                                                    
  2279. set &tmplib..YQstep1var_tr;                                                            
  2280. array testmiss(*) _numeric_;                                            
  2281. do i = 1 to dim(testmiss);                                              
  2282.     if testmiss(i)=. then testmiss(i)=0;                                    
  2283. end;
  2284. drop _NAME_;
  2285. run;
  2286.  
  2287. data &tmplib..YQstep1varBL_tr(drop=i);                                                    
  2288. set &tmplib..YQstep1varBL_tr;                                                            
  2289. array testmiss(*) _numeric_;                                            
  2290. do i = 1 to dim(testmiss);                                              
  2291.     if testmiss(i)=. then testmiss(i)=0;                                    
  2292. end;
  2293. drop _NAME_;
  2294. run;
  2295.  
  2296. data &tmplib..YQstep1varBL_RC_tr_final(drop=i);                                                    
  2297. set &tmplib..YQstep1varBL_RC_tr_final;                                                            
  2298. array testmiss(*) _numeric_;                                            
  2299. do i = 1 to dim(testmiss);                                              
  2300.     if testmiss(i)=. then testmiss(i)=0;                                    
  2301. end;
  2302. drop _NAME_;
  2303. run;
  2304.  
  2305. data &tmplib..YQstep1varRC_tr_Final(drop=i);                                                    
  2306. set &tmplib..YQstep1varRC_tr_Final;                                                            
  2307. array testmiss(*) _numeric_;                                            
  2308. do i = 1 to dim(testmiss);                                              
  2309.     if testmiss(i)=. then testmiss(i)=0;                                    
  2310. end;
  2311. drop _NAME_;
  2312. run;
  2313.  
  2314. data &tmplib..YQstep1varKBS_tr_Final(drop=i);                                                    
  2315. set &tmplib..YQstep1varKBS_tr_Final;                                                            
  2316. array testmiss(*) _numeric_;                                            
  2317. do i = 1 to dim(testmiss);                                              
  2318.     if testmiss(i)=. then testmiss(i)=0;                                    
  2319. end;
  2320. drop _NAME_;
  2321. run;
  2322.  
  2323. data &tmplib..YMstep1var_tr(drop=i);                                                    
  2324. set &tmplib..YMstep1var_tr;                                                            
  2325. array testmiss(*) _numeric_;                                            
  2326. do i = 1 to dim(testmiss);                                              
  2327.     if testmiss(i)=. then testmiss(i)=0;                                    
  2328. end;
  2329. drop _NAME_;
  2330. run;
  2331.  
  2332. data &tmplib..YMstep1varBL_tr(drop=i);                                                    
  2333. set &tmplib..YMstep1varBL_tr;                                                            
  2334. array testmiss(*) _numeric_;                                            
  2335. do i = 1 to dim(testmiss);                                              
  2336.     if testmiss(i)=. then testmiss(i)=0;                                    
  2337. end;
  2338. drop _NAME_;
  2339. run;
  2340.  
  2341. data &tmplib..YMstep1varBL_RC_tr_final(drop=i);                                                    
  2342. set &tmplib..YMstep1varBL_RC_tr_final;                                                            
  2343. array testmiss(*) _numeric_;                                            
  2344. do i = 1 to dim(testmiss);                                              
  2345.     if testmiss(i)=. then testmiss(i)=0;                                    
  2346. end;
  2347. drop _NAME_;
  2348. run;
  2349.  
  2350. data &tmplib..YMstep1varRC_tr_Final(drop=i);                                                    
  2351. set &tmplib..YMstep1varRC_tr_Final;                                                            
  2352. array testmiss(*) _numeric_;                                            
  2353. do i = 1 to dim(testmiss);                                              
  2354.     if testmiss(i)=. then testmiss(i)=0;                                    
  2355. end;
  2356. drop _NAME_;
  2357. run;
  2358.  
  2359. data &tmplib..YMstep1varKBS_tr_Final(drop=i);                                                    
  2360. set &tmplib..YMstep1varKBS_tr_Final;                                                            
  2361. array testmiss(*) _numeric_;                                            
  2362. do i = 1 to dim(testmiss);                                              
  2363.     if testmiss(i)=. then testmiss(i)=0;                                    
  2364. end;
  2365. drop _NAME_;
  2366. run;
  2367.  
  2368. data &tmplib..YMstep3var_tr(drop=i);                                                    
  2369. set &tmplib..YMstep3var_tr;                                                            
  2370. array testmiss(*) _numeric_;                                            
  2371. do i = 1 to dim(testmiss);                                              
  2372.     if testmiss(i)=. then testmiss(i)=0;                                    
  2373. end;
  2374. drop _NAME_;
  2375. run;
  2376.  
  2377. data &tmplib..YMstep4var_tr_final(drop=i);                                                    
  2378. set &tmplib..YMstep4var_tr_final;                                                            
  2379. array testmiss(*) _numeric_;                                            
  2380. do i = 1 to dim(testmiss);                                              
  2381.     if testmiss(i)=. then testmiss(i)=0;                                    
  2382. end;
  2383. drop _NAME_;
  2384. run;
  2385.  
  2386. data &tmplib..YMstep4varBL_tr_final(drop=i);                                                    
  2387. set &tmplib..YMstep4varBL_tr_final;                                                            
  2388. array testmiss(*) _numeric_;                                            
  2389. do i = 1 to dim(testmiss);                                              
  2390.     if testmiss(i)=. then testmiss(i)=0;                                    
  2391. end;
  2392. drop _NAME_;
  2393. run;
  2394.  
  2395. /********************************/
  2396. /*  % change - YY, QQ and MM    */
  2397. /********************************/
  2398.  
  2399. /*
  2400. Tables to update:
  2401. -Quarterly
  2402.     YQstep1var_tr
  2403.     YQstep1varBL_tr
  2404.     YQstep1varBL_RC_tr_Final
  2405.     YQstep1varRC_tr_Final
  2406.     YQstep1varKBS_tr_Final
  2407. -Monthly
  2408.     YMstep1var_tr
  2409.     YMstep1varBL_tr
  2410.     YMstep1varBL_RC_tr_Final
  2411.     YMstep1varRC_tr_Final
  2412.     YMstep1varKBS_tr_Final
  2413.     YMstep3var_tr
  2414.     YMstep4var_tr_final
  2415.     YMstep4varBL_tr_final
  2416.  
  2417. -Variables
  2418. &mt1
  2419. &mt2
  2420. &mt3
  2421. */
  2422. /*
  2423. data &tmplib..YMstep4var&suffix._tr_final;
  2424. set &tmplib..YMstep4var&suffix._tr_final;
  2425. run;
  2426. %let start=%eval(&mt1-&step);
  2427. %global end;
  2428. %let end=&mt1;
  2429.  
  2430. *%put &start.;
  2431. *%put &end.;
  2432.  
  2433. %global Q;
  2434. %let Q=Q;
  2435. %let v=v;
  2436. %global latestq;
  2437. %let latestq=&v&end&Q&eq;
  2438.  
  2439. %let v=v;
  2440. %let firsty=&v&start;
  2441.  
  2442. created in lm macro in FRAN_1_macros
  2443. &prevm
  2444. */
  2445.  
  2446. /*
  2447. yychange_q=[(2013Q1+...+2013Q3)-(2012Q1+...+2012Q3)]/(2012Q1+...+2012Q3)
  2448. yychange_m=[(2013M01+...+2013M11)-(2012M01+...+2012M11)]/(2012M01+...+2012M11)
  2449. qqchange=(2013Q3-2012Q3)/2012Q3
  2450. qqchange2=(2013Q3-2013Q2)/2013Q2
  2451. mmchange=(2013M11-2012M11)/2012M11
  2452. mmchange2=(2013M11-2013M10)/(2013M10)
  2453. */
  2454.  
  2455. %previous;
  2456.  
  2457. *%put _all_;
  2458.  
  2459. /************************/
  2460. /*  Calculate % changes */
  2461. /************************/
  2462. /*
  2463. yychange_q=[(2013Q1+...+2013Q3)-(2012Q1+...+2012Q3)]/(2012Q1+...+2012Q3)
  2464. yychange_m=[(2013M01+...+2013M11)-(2012M01+...+2012M11)]/(2012M01+...+2012M11)
  2465. qqchange=(2013Q3-2012Q3)/2012Q3
  2466. qqchange2=(2013Q3-2013Q2)/2013Q2
  2467. mmchange=(2013M11-2012M11)/2012M11
  2468. mmchange2=(2013M11-2013M10)/(2013M10)
  2469. */
  2470. /*
  2471. yychange_q:
  2472.     latestq_ty
  2473.     firstq_ty
  2474.     latestq_py
  2475.     firstq_py
  2476. qqchange:
  2477.     latestq_ty
  2478.     latestq_py
  2479. qqchange2:
  2480.     latestq_ty
  2481.     prevq
  2482.  
  2483. yychange_q=[(2013Q1+...+2013Q3)-(2012Q1+...+2012Q3)]/(2012Q1+...+2012Q3)
  2484. qqchange=(2013Q3-2012Q3)/2012Q3
  2485. qqchange2=(2013Q3-2013Q2)/2013Q2
  2486. */
  2487. /*  Quarterly   */
  2488. data &tmplib..YQstep1var_tr;
  2489. set &tmplib..YQstep1var_tr;
  2490.     qqchange2=(&latestq_ty-&prevq)/&prevq;
  2491.     qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
  2492.     yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
  2493.     format qqchange2 qqchange yychange percentn10.1;
  2494. run;
  2495.  
  2496. data &tmplib..YQstep1varBL_tr;
  2497. set &tmplib..YQstep1varBL_tr;
  2498.     qqchange2=(&latestq_ty-&prevq)/&prevq;
  2499.     qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
  2500.     yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
  2501.     format qqchange2 qqchange yychange percentn10.1;
  2502. run;
  2503.  
  2504. data &tmplib..YQstep1varBL_RC_tr_Final;
  2505. set &tmplib..YQstep1varBL_RC_tr_Final;
  2506.     qqchange2=(&latestq_ty-&prevq)/&prevq;
  2507.     qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
  2508.     yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
  2509.     format qqchange2 qqchange yychange percentn10.1;
  2510. run;
  2511.  
  2512. data &tmplib..YQstep1varRC_tr_Final;
  2513. set &tmplib..YQstep1varRC_tr_Final;
  2514.     qqchange2=(&latestq_ty-&prevq)/&prevq;
  2515.     qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
  2516.     yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
  2517.     format qqchange2 qqchange yychange percentn10.1;
  2518. run;
  2519.  
  2520. data &tmplib..YQstep1varKBS_tr_Final;
  2521. set &tmplib..YQstep1varKBS_tr_Final;
  2522.     qqchange2=(&latestq_ty-&prevq)/&prevq;
  2523.     qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
  2524.     yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
  2525.     format qqchange2 qqchange yychange percentn10.1;
  2526. run;
  2527.  
  2528. /*
  2529. yychange_m:
  2530.     latestm_ty
  2531.     firstm_ty
  2532.     latestm_py
  2533.     firstm_py
  2534. mmchange:
  2535.     latestm_ty
  2536.     latestm_py
  2537. mmchange2:
  2538.     latestm_ty
  2539.     prevm
  2540.  
  2541. yychange_m=[(2013M01+...+2013M11)-(2012M01+...+2012M11)]/(2012M01+...+2012M11)
  2542. mmchange=(2013M11-2012M11)/2012M11
  2543. mmchange2=(2013M11-2013M10)/(2013M10)
  2544. */
  2545. /*  Monthly */
  2546. data &tmplib..YMstep1var_tr;
  2547. set &tmplib..YMstep1var_tr;
  2548.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2549.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2550.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2551.     format mmchange2 mmchange yychange percentn10.1;
  2552. run;
  2553.  
  2554. data &tmplib..YMstep1varBL_tr;
  2555. set &tmplib..YMstep1varBL_tr;
  2556.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2557.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2558.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2559.     format mmchange2 mmchange yychange percentn10.1;
  2560. run;
  2561.  
  2562. data &tmplib..YMstep1varBL_RC_tr_Final;
  2563. set &tmplib..YMstep1varBL_RC_tr_Final;
  2564.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2565.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2566.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2567.     format mmchange2 mmchange yychange percentn10.1;
  2568. run;
  2569.  
  2570. data &tmplib..YMstep1varRC_tr_Final;
  2571. set &tmplib..YMstep1varRC_tr_Final;
  2572.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2573.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2574.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2575.     format mmchange2 mmchange yychange percentn10.1;
  2576. run;
  2577.  
  2578. data &tmplib..YMstep1varKBS_tr_Final;
  2579. set &tmplib..YMstep1varKBS_tr_Final;
  2580.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2581.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2582.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2583.     format mmchange2 mmchange yychange percentn10.1;
  2584. run;
  2585.  
  2586. data &tmplib..YMstep3var_tr;
  2587. set &tmplib..YMstep3var_tr;
  2588.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2589.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2590.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2591.     format mmchange2 mmchange yychange percentn10.1;
  2592. run;
  2593.  
  2594. data &tmplib..YMstep4var_tr_final;
  2595. set &tmplib..YMstep4var_tr_final;
  2596.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2597.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2598.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2599.     format mmchange2 mmchange yychange percentn10.1;
  2600. run;
  2601.  
  2602. data &tmplib..YMstep4varBL_tr_final;
  2603. set &tmplib..YMstep4varBL_tr_final;
  2604.     mmchange2=(&latestm_ty-&prevm)/&prevm;
  2605.     mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
  2606.     yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
  2607.     format mmchange2 mmchange yychange percentn10.1;
  2608. run;
  2609.  
  2610. /****************************************************************************************************/
  2611. /*
  2612. ___________.__    .__         .__           __  .__             ___________ _______  ________ ._._._.
  2613. \__    ___/|  |__ |__| ______ |__| ______ _/  |_|  |__   ____   \_   _____/ \      \ \______ \| | | |
  2614.   |    |   |  |  \|  |/  ___/ |  |/  ___/ \   __\  |  \_/ __ \   |    __)_  /   |   \ |    |  \ | | |
  2615.   |    |   |   Y  \  |\___ \  |  |\___ \   |  | |   Y  \  ___/   |        \/    |    \|    `   \|\|\|
  2616.   |____|   |___|  /__/____  > |__/____  >  |__| |___|  /\___  > /_______  /\____|__  /_______  /_____
  2617.                 \/        \/          \/             \/     \/          \/         \/        \/\/\/\/
  2618. */
  2619. /*****************************************************************************************************/
  2620.  
  2621. /*
  2622.  _____                _           _   _                                  
  2623. /  __ \              | |         | | | |                                  
  2624. | /  \/_ __ ___  __ _| |_ ___  __| | | |__  _   _                        
  2625. | |   | '__/ _ \/ _` | __/ _ \/ _` | | '_ \| | | |                        
  2626. | \__/\ | |  __/ (_| | ||  __/ (_| | | |_) | |_| |                        
  2627.  \____/_|  \___|\__,_|\__\___|\__,_| |_.__/ \__, |                        
  2628.                                              __/ |                        
  2629.                                             |___/                        
  2630.  _   __                      _   _____ _                          _    _  
  2631. | | / /                     | | |  _  | |                        | |  (_)
  2632. | |/ /  ___  _ __ _ __   ___| | | | | | |___ _________      _____| | ___  
  2633. |    \ / _ \| '__| '_ \ / _ \ | | | | | / __|_  / _ \ \ /\ / / __| |/ / |
  2634. | |\  \ (_) | |  | | | |  __/ | \ \_/ / \__ \/ /  __/\ V  V /\__ \   <| |
  2635. \_| \_/\___/|_|  |_| |_|\___|_|  \___/|_|___/___\___| \_/\_/ |___/_|\_\_|                                                      
  2636.                                                                                                                                                                                                                                                                                                                                                                                                                                          
  2637. */
  2638. *%put _all_;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement