Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- ______ _____ _ _ _ _ _
- | ____| __ \ /\ | \ | | | | | | | |
- | |__ | |__) | / \ | \| | | |_ __ _| |__ | | ___ ___
- | __| | _ / / /\ \ | . ` | | __/ _` | '_ \| |/ _ \/ __|
- | | | | \ \ / ____ \| |\ | | || (_| | |_) | | __/\__ \
- |_| |_| \_\/_/ \_\_| \_| \__\__,_|_.__/|_|\___||___/
- __ _ _ _
- / _| (_) (_) | |
- | |_ ___ _ __ _ _ __ ___ _ __ ___ _ __ _ _ __ __ _ _ __ | |_
- | _/ _ \| '__| | | '_ ` _ \| '_ ` _ \| |/ _` | '__/ _` | '_ \| __|
- | || (_) | | | | | | | | | | | | | | | (_| | | | (_| | | | | |_
- |_| \___/|_| |_|_| |_| |_|_| |_| |_|_|\__, |_| \__,_|_| |_|\__|
- __/ |
- _ _ _ _ |___/
- | | (_) | (_) |
- _ __ __ _| |_ _ ___ _ __ __ _| |_| |_ _ _
- | '_ \ / _` | __| |/ _ \| '_ \ / _` | | | __| | | |
- | | | | (_| | |_| | (_) | | | | (_| | | | |_| |_| |
- |_| |_|\__,_|\__|_|\___/|_| |_|\__,_|_|_|\__|\__, |
- __/ |
- |___/
- */
- /* Choose indicator => ok */
- %global ind;
- %let ind="IBC-1A","IBC-1B","FAC-2","ILL-3","REF-4","ASY-5","FAL-6","RET-7A","RET-7B";
- *%put &ind.;
- /* Temporary library => ok*/
- *%global tmplib;
- *%let tmplib=work;
- *%let tmplib=dane;
- *libname &tmplib '\\aofrontex.local\frontex-shared\Restricted Area\Operations Division\RAU\RAU Work\INTERNSHIP\Kornel\Library';
- /* Choose dataset - most probably will be set fixed => ok */
- *%global set;
- *%let set=temp;
- /* FRAN */
- *%global inlib;
- *%let inlib=shredstg;
- *%global set2;
- *%let set2=fran_analysis;
- *libname &inlib '\\aofrontex.local\sas-shareddrive\SASData';
- /********************************************************************************/
- /* */
- /* Yearly data for nationality - yearly shares in all immigrant nationalities */
- /* */
- /********************************************************************************/
- %time_secure;
- %let start=%eval(&mt1-&step);
- %global end;
- %let end=&mt1;
- *%put &start.;
- *%put &end.;
- /* Choose time span - yearly*/
- %years(start=&start,end=&end);
- /* Totals over years for chosen variable - nationality and chosen level */
- proc sql noprint;
- create table &tmplib..Ystep1var as
- select distinct &variable, YEAR,Indicator, sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YEAR in (&yrs) and Indicator ne ""
- group by &variable, YEAR, Indicator
- order by Indicator, YEAR;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Ystep1var prefix=v
- out=&tmplib..Ystep1var_tr;
- by Indicator;
- id YEAR;
- var sum_Total;
- run;
- data &tmplib..Ystep1var_tr;
- set &tmplib..Ystep1var_tr;
- drop _NAME_;
- run;
- /* Totals over years for all nationalities - chosen indicators*/
- proc sql noprint;
- create table &tmplib..Ystep2all as
- select distinct YEAR,Indicator, sum(Total) as sum_Total
- from &inlib..&set2.
- where YEAR in (&yrs) and Indicator ne ""
- group by YEAR, Indicator
- order by Indicator, YEAR;
- quit;
- /* Calculate the shares we need */
- proc sql noprint;
- create table &tmplib..Yshares as
- select distinct a.Indicator, a.&variable, a.YEAR, a.sum_Total/b.sum_Total as Share format=percentn10.2
- from &tmplib..Ystep1var as a, &tmplib..Ystep2all as b
- where a.YEAR=b.YEAR and a.Indicator=b.Indicator
- order by Indicator, YEAR, &variable;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Yshares name=share prefix=v
- out=&tmplib..Yshares_tr;
- by Indicator;
- id YEAR;
- var Share;
- run;
- /* Put the chosen variable at the beginning of the dataset */
- data &tmplib..Yshares_tr;
- set &tmplib..Yshares_tr;
- drop share;
- &variable="&val";
- run;
- data &tmplib..Yshares_tr;
- retain &variable;
- set &tmplib..Yshares_tr;
- run;
- /* Tested - works for years */
- /************************************************************************/
- /* */
- /* Divide yearly totals by another variable */
- /* */
- /************************************************************************/
- /*********************/
- /* Reporting_country */
- /*********************/
- /* Choose 2nd dividing variable => ok */
- %global variable2;
- %global suffix;
- %let variable2=reporting_country_label;
- %let suffix=RC;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Ystep1var&suffix as
- select distinct &variable,YEAR,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YEAR in (&yrs) and Indicator ne ""
- group by &variable, YEAR, Indicator, &variable2
- order by Indicator, &variable2, YEAR;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Ystep1var&suffix prefix=v
- out=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- id YEAR;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Ystep1var&suffix._tr;
- set &tmplib..Ystep1var&suffix._tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Ystep1var&suffix._tr;
- retain &variable;
- set &tmplib..Ystep1var&suffix._tr;
- drop _NAME_;
- run;
- /************************/
- /* KeyBorderSection */
- /************************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Ystep1var&suffix as
- select distinct &variable,YEAR,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YEAR in (&yrs) and Indicator ne ""
- group by &variable, YEAR, Indicator, &variable2
- order by Indicator, &variable2, YEAR;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Ystep1var&suffix prefix=v
- out=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- id YEAR;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Ystep1var&suffix._tr;
- set &tmplib..Ystep1var&suffix._tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Ystep1var&suffix._tr;
- retain &variable;
- set &tmplib..Ystep1var&suffix._tr;
- drop _NAME_;
- run;
- /******************/
- /* BorderLocation */
- /******************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=BorderLocation;
- %let suffix=BL;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Ystep1var&suffix as
- select distinct &variable,YEAR,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YEAR in (&yrs) and Indicator ne "" and &variable2 ne ""
- group by &variable, YEAR, Indicator, &variable2
- order by Indicator, &variable2, YEAR, sum_Total;
- quit;
- /* Change empty Border Locations to Inland */
- data &tmplib..Ystep1var&suffix;
- set &tmplib..Ystep1var&suffix;
- if &variable2 eq "" then &variable2="Inland";
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Ystep1var&suffix prefix=v
- out=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- id YEAR;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Ystep1var&suffix._tr;
- set &tmplib..Ystep1var&suffix._tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Ystep1var&suffix._tr;
- retain &variable;
- set &tmplib..Ystep1var&suffix._tr;
- drop _NAME_;
- run;
- /********************************************/
- /* BorderLocation by top n MemberStates */
- /********************************************/
- /* Choose 3rd dividing variable => ok */
- %global variable3;
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- /* Totals over years for chosen variable - nationality by variable3 - reporting country */
- proc sql noprint;
- create table &tmplib..Ystep1var&suffix._&suffix2 as
- select distinct &variable,YEAR,Indicator,&variable2,&variable3,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YEAR in (&yrs) and Indicator ne "" and &variable2 ne ""
- group by &variable, YEAR, Indicator, &variable2, &variable3
- order by Indicator, &variable2, &variable3, YEAR, sum_Total;
- quit;
- /* Change empty Border Locations to Inland */
- data &tmplib..Ystep1var&suffix._&suffix2;
- set &tmplib..Ystep1var&suffix._&suffix2;
- if &variable2 eq "" then &variable2="Inland";
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Ystep1var&suffix._&suffix2 prefix=v
- out=&tmplib..Ystep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- id YEAR;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Ystep1var&suffix._&suffix2._tr;
- set &tmplib..Ystep1var&suffix._&suffix2._tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Ystep1var&suffix._&suffix2._tr;
- retain &variable;
- set &tmplib..Ystep1var&suffix._&suffix2._tr;
- drop _NAME_;
- run;
- /************************************************************************************/
- /* */
- /* Quaterly data for nationality - quaterly shares in all immigrant nationalities */
- /* */
- /************************************************************************************/
- %global bq;
- %let bq=1;
- *%put &qtr1.;
- *%put &bq.;
- %global eq;
- %let eq=&mt2;
- *%put &qtr2.;
- *%put &eq.;
- /* Choose time span */
- %quarters(start=&start,end=&end,bq=&bq,eq=&eq);
- /* Totals over quartesrs for chosen variable - nationality and chosen level */
- proc sql noprint;
- create table &tmplib..Qstep1var as
- select distinct &variable, YYYYQ,Indicator, sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
- group by &variable, YYYYQ, Indicator
- order by Indicator, YYYYQ;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Qstep1var prefix=v
- out=&tmplib..Qstep1var_tr;
- by Indicator;
- id YYYYQ;
- var sum_Total;
- run;
- data &tmplib..Qstep1var_tr;
- set &tmplib..Qstep1var_tr;
- drop _NAME_;
- run;
- /* Totals over quarters for all nationalities */
- proc sql noprint;
- create table &tmplib..Qstep2all as
- select distinct Indicator, YYYYQ, sum(Total) as sum_Total
- from &inlib..&set2.
- where YYYYQ in (&qtrs) and Indicator ne ""
- group by YYYYQ, Indicator
- order by Indicator, YYYYQ;
- quit;
- /* Calculate the shares we need */
- proc sql noprint;
- create table &tmplib..Qshares as
- select distinct a.Indicator, a.&variable, a.YYYYQ, a.sum_Total/b.sum_Total as Share format=percentn10.2
- from &tmplib..Qstep1var as a, &tmplib..Qstep2all as b
- where a.YYYYQ=b.YYYYQ and a.Indicator=b.Indicator
- order by Indicator, YYYYQ;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Qshares name=share prefix=v
- out=&tmplib..Qshares_tr;
- by Indicator;
- id YYYYQ;
- var Share;
- run;
- /* Put the chosen variable at the beginning of the dataset */
- data &tmplib..Qshares_tr;
- set &tmplib..Qshares_tr;
- drop share;
- &variable="&val";
- run;
- data &tmplib..Qshares_tr;
- retain &variable;
- set &tmplib..Qshares_tr;
- run;
- /* Tested - works for quarters */
- /************************************************************************/
- /* */
- /* Divide quarterly totals by another variable */
- /* */
- /************************************************************************/
- /*********************/
- /* Reporting_country */
- /*********************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=reporting_country_label;
- %let suffix=RC;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Qstep1var&suffix as
- select distinct &variable,YYYYQ,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
- group by &variable, YYYYQ, Indicator, &variable2
- order by Indicator, &variable2;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Qstep1var&suffix prefix=v
- out=&tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- id YYYYQ;
- var sum_Total;
- run;
- data &tmplib..Qstep1var&suffix._tr;
- set &tmplib..Qstep1var&suffix._tr;
- drop _NAME_;
- run;
- /************************/
- /* KeyBorderSection */
- /************************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- /* Totals over years for chosen variable - nationality by variable2 - BorderLocation */
- proc sql noprint;
- create table &tmplib..Qstep1var&suffix as
- select distinct &variable,YYYYQ,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
- group by &variable, YYYYQ, Indicator, &variable2
- order by Indicator, &variable2, YYYYQ;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Qstep1var&suffix prefix=v
- out=&tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- id YYYYQ;
- var sum_Total;
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Qstep1var&suffix._tr;
- set &tmplib..Qstep1var&suffix._tr;
- drop _NAME_;
- run;
- /******************/
- /* BorderLocation */
- /******************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=BorderLocation;
- %let suffix=BL;
- /* Totals over years for chosen variable - nationality by variable2 - BorderLocation */
- proc sql noprint;
- create table &tmplib..Qstep1var&suffix as
- select distinct &variable,YYYYQ,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
- group by &variable, YYYYQ, Indicator, &variable2
- order by Indicator, &variable2, YYYYQ;
- quit;
- /* Change empty Border Locations to Inland */
- data &tmplib..Qstep1var&suffix;
- set &tmplib..Qstep1var&suffix;
- if &variable2 eq "" then &variable2="Inland";
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Qstep1var&suffix prefix=v
- out=&tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- id YYYYQ;
- var sum_Total;
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Qstep1var&suffix._tr;
- set &tmplib..Qstep1var&suffix._tr;
- drop _NAME_;
- run;
- /********************************************/
- /* BorderLocation by top n MemberStates */
- /********************************************/
- /* Choose 3rd dividing variable => ok */
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Qstep1var&suffix._&suffix2 as
- select distinct &variable,YYYYQ,Indicator,&variable2,&variable3,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYQ in (&qtrs) and Indicator ne ""
- group by &variable, YYYYQ, Indicator, &variable2, &variable3
- order by Indicator, &variable2, &variable3, YYYYQ, sum_Total;
- quit;
- /* Change empty Border Locations to Inland */
- data &tmplib..Qstep1var&suffix._&suffix2;
- set &tmplib..Qstep1var&suffix._&suffix2;
- if &variable2 eq "" then &variable2="Inland";
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Qstep1var&suffix._&suffix2 prefix=v
- out=&tmplib..Qstep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- id YYYYQ;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Qstep1var&suffix._&suffix2._tr;
- set &tmplib..Qstep1var&suffix._&suffix2._tr;
- &variable="&val";
- run;
- /************************************************************************************/
- /* */
- /* Monthly data for nationality - monthly shares in all immigrant nationalities */
- /* */
- /************************************************************************************/
- %global bm;
- %let bm=1;
- *%put &mth1.;
- *%put &bm.;
- %global em;
- %let em=&mt3;
- *%put &mth2.;
- *%put &em.;
- /*
- %global mth;
- %let mth=1.0;
- %put &mth.;
- %let bm=%sysfunc(putn(%sysfunc(inputn(&mth,bestw.)),bestd4.));
- %put &bm.;
- */
- /* Choose time span */
- %months(start=&start,end=&end,bm=&bm,em=&em);
- /* Totals over months for chosen nationality */
- proc sql noprint;
- create table &tmplib..Mstep1var as
- select distinct &variable, YYYYMM,Indicator, sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYMM in (&mths) and Indicator ne ""
- group by &variable, YYYYMM, Indicator
- order by Indicator, YYYYMM;
- quit;
- data &tmplib..Mstep1var;
- set &tmplib..Mstep1var;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Reshape -> long to wide */
- proc transpose data=&tmplib..Mstep1var prefix=v
- out=&tmplib..Mstep1var_tr;
- by Indicator;
- id YYYYMM;
- var sum_Total;
- run;
- data &tmplib..Mstep1var_tr;
- set &tmplib..Mstep1var_tr;
- drop _NAME_;
- run;
- /* Totals over months for all nationalities */
- proc sql noprint;
- create table &tmplib..Mstep2all as
- select distinct Indicator, YYYYMM, sum(Total) as sum_Total
- from &inlib..&set2.
- where YYYYMM in (&mths) and Indicator ne ""
- group by YYYYMM, Indicator
- order by Indicator, YYYYMM;
- quit;
- data &tmplib..Mstep2all;
- set &tmplib..Mstep2all;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Calculate the shares we need */
- proc sql noprint;
- create table &tmplib..Mshares as
- select distinct a.Indicator, a.&variable, a.YYYYMM, a.sum_Total/b.sum_Total as Share format=percentn10.2
- from &tmplib..Mstep1var as a, &tmplib..Mstep2all as b
- where a.YYYYMM=b.YYYYMM and a.Indicator=b.Indicator
- order by Indicator, YYYYMM;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Mshares name=share prefix=v
- out=&tmplib..Mshares_tr;
- by Indicator;
- id YYYYMM;
- var Share;
- run;
- /* Put the chosen variable at the beginning of the dataset */
- data &tmplib..Mshares_tr;
- set &tmplib..Mshares_tr;
- &variable="&val";
- drop share;
- run;
- data &tmplib..Mshares_tr;
- retain &variable;
- set &tmplib..Mshares_tr;
- run;
- /* Tested - works for months */
- /************************************************************************/
- /* */
- /* Divide monthly totals by another variable */
- /* */
- /************************************************************************/
- /* Reporting_country */
- /* Choose 2nd dividing variable => ok */
- %let variable2=reporting_country_label;
- %let suffix=RC;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Mstep1var&suffix as
- select distinct &variable,YYYYMM,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYMM in (&mths) and Indicator ne ""
- group by &variable, YYYYMM, Indicator, &variable2
- order by Indicator, &variable2, YYYYMM;
- quit;
- /* Rename the months */
- data &tmplib..Mstep1var&suffix;
- set &tmplib..Mstep1var&suffix;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Mstep1var&suffix prefix=v
- out=&tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- id YYYYMM;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Mstep1var&suffix._tr;
- set &tmplib..Mstep1var&suffix._tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Mstep1var&suffix._tr;
- retain &variable;
- set &tmplib..Mstep1var&suffix._tr;
- drop _NAME_;
- run;
- /* KeyBorderSection */
- /* Choose 2nd dividing variable => ok */
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Mstep1var&suffix as
- select distinct &variable,YYYYMM,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYMM in (&mths) and Indicator ne ""
- group by &variable, YYYYMM, Indicator, &variable2
- order by Indicator, &variable2, YYYYMM;
- quit;
- /* Rename the months */
- data &tmplib..Mstep1var&suffix;
- set &tmplib..Mstep1var&suffix;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Mstep1var&suffix prefix=v
- out=&tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- id YYYYMM;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Mstep1var&suffix._tr;
- set &tmplib..Mstep1var&suffix._tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Mstep1var&suffix._tr;
- retain &variable;
- set &tmplib..Mstep1var&suffix._tr;
- drop _NAME_;
- run;
- /* BorderLocation */
- /* Choose 2nd dividing variable => ok */
- %let variable2=BorderLocation;
- %let suffix=BL;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Mstep1var&suffix as
- select distinct &variable,YYYYMM,Indicator,&variable2,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYMM in (&mths) and &variable2 ne "" and Indicator ne ""
- group by &variable, YYYYMM, Indicator, &variable2
- order by Indicator, &variable2, YYYYMM;
- quit;
- /* Change empty Border Locations to Inland */
- data &tmplib..Mstep1var&suffix;
- set &tmplib..Mstep1var&suffix;
- if &variable2 eq "" then &variable2="Inland";
- run;
- /* Rename the months */
- data &tmplib..Mstep1var&suffix;
- set &tmplib..Mstep1var&suffix;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Mstep1var&suffix prefix=v
- out=&tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- id YYYYMM;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Mstep1var&suffix._tr;
- set &tmplib..Mstep1var&suffix._tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..Mstep1var&suffix._tr;
- retain &variable;
- set &tmplib..Mstep1var&suffix._tr;
- drop _NAME_;
- run;
- /********************************************/
- /* BorderLocation by top n MemberStates */
- /********************************************/
- /* Choose 3rd dividing variable => ok */
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- /* Totals over years for chosen variable - nationality by variable2 - reporting country */
- proc sql noprint;
- create table &tmplib..Mstep1var&suffix._&suffix2 as
- select distinct &variable,YYYYMM,Indicator,&variable2,&variable3,sum(Total) as sum_Total
- from &inlib..&set2.
- where &variable="&val" and YYYYMM in (&mths) and &variable2 ne "" and Indicator ne ""
- group by &variable, YYYYMM, Indicator, &variable2, &variable3
- order by Indicator, &variable2, &variable3, YYYYMM, sum_Total;
- quit;
- /* Rename the months */
- data &tmplib..Mstep1var&suffix._&suffix2;
- set &tmplib..Mstep1var&suffix._&suffix2;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Change empty Border Locations to Inland */
- data &tmplib..Mstep1var&suffix._&suffix2;
- set &tmplib..Mstep1var&suffix._&suffix2;
- if &variable2 eq "" then &variable2="Inland";
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Mstep1var&suffix._&suffix2 prefix=v
- out=&tmplib..Mstep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- id YYYYMM;
- var sum_Total;
- run;
- /* Insert the chosen variable into dataset */
- data &tmplib..Mstep1var&suffix._&suffix2._tr;
- set &tmplib..Mstep1var&suffix._&suffix2._tr;
- &variable="&val";
- run;
- /************************************************/
- /* */
- /* Choose top n totals for indicator, sort */
- /* aggregate the rest */
- /* */
- /************************************************/
- /*
- latest year
- %let end=2012;
- latest quarter
- %let eq=3;
- 2012Q3
- */
- %global Q;
- %let Q=Q;
- %let v=v;
- %global latestq;
- %let latestq=&v&end&Q&eq;
- %let firsty=&v&start;
- /****************************/
- /* Quarterly with Yearly */
- /****************************/
- proc sort data=&tmplib..Ystep1var_tr;
- by Indicator;
- run;
- proc sort data=&tmplib..Qstep1var_tr;
- by Indicator;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YQstep1var_tr;
- merge &tmplib..Ystep1var_tr &tmplib..Qstep1var_tr;
- by Indicator;
- run;
- proc sort Data=&tmplib..YQstep1var_tr;
- by Indicator;
- run;
- data &tmplib..YQstep1var_tr;
- set &tmplib..YQstep1var_tr;
- &variable="&val";
- run;
- /* Put the chosen variable at the beginning os the dataset */
- data &tmplib..YQstep1var_tr;
- retain &variable;
- set &tmplib..YQstep1var_tr;
- run;
- /********************/
- /* Fancy sorting */
- /********************/
- data &tmplib..YQstep1var_tr;
- set &tmplib..YQstep1var_tr;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- run;
- proc sort Data=&tmplib..YQstep1var_tr;
- by sort1 descending &latestq;
- run;
- data &tmplib..YQstep1var_tr;
- set &tmplib..YQstep1var_tr;
- drop sort1;
- run;
- /*********************/
- /* Reporting_country */
- /*********************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=reporting_country_label;
- %let suffix=RC;
- proc sort data=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- run;
- proc sort data=&tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YQstep1var&suffix._tr;
- retain &variable;
- merge &tmplib..Ystep1var&suffix._tr &tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- run;
- data &tmplib..YQshares_tr;
- retain &variable;
- merge &tmplib..Yshares_tr &tmplib..Qshares_tr;
- by Indicator;
- &variable="&val";
- run;
- /****************************************/
- /* Sort by Indicator and latest quarter */
- /****************************************/
- proc sort Data=&tmplib..YQstep1var&suffix._tr ;
- by Indicator descending &latestq;
- run;
- /************************************/
- /* Choose top n rows by indicators */
- /************************************/
- %global n;
- %let n=⊤
- data &tmplib..YQstep1var&suffix._tr_top;
- set &tmplib..YQstep1var&suffix._tr;
- count + 1;
- by Indicator;
- if first.Indicator then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_others;
- set &tmplib..YQstep1var&suffix._tr_top;
- if count<=&n then output &tmplib..YQstep1var&suffix._tr_topn;
- if count>&n then output &tmplib..YQstep1var&suffix._tr_others;
- run;
- proc means data=&tmplib..YQstep1var&suffix._tr_others noprint;
- class Indicator;
- var &firsty--&latestq;
- output out=&tmplib..YQstep1var&suffix._tr_othersagg sum=;
- run;
- data &tmplib..YQstep1var&suffix._tr_othersagg;
- set &tmplib..YQstep1var&suffix._tr_othersagg;
- where Indicator ne "";
- drop _TYPE_ _FREQ_;
- &variable="&val";
- &variable2="others";
- run;
- data &tmplib..YQstep1var&suffix._tr_final;
- set &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_othersagg;
- run;
- /* Fancy sorting */
- data &tmplib..YQstep1var&suffix._tr_final;
- set &tmplib..YQstep1var&suffix._tr_final;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- if &variable2 ne "others" then sort2=1;
- if &variable2 = "others" then sort2=2;
- run;
- proc sort Data=&tmplib..YQstep1var&suffix._tr_final;
- by sort1 sort2 descending &latestq;
- run;
- data &tmplib..YQstep1var&suffix._tr_final;
- set &tmplib..YQstep1var&suffix._tr_final;
- drop sort1 sort2 count;
- run;
- /************************/
- /* KeyBorderSection */
- /************************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- proc sort data=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- run;
- proc sort Data=&tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YQstep1var&suffix._tr;
- merge &tmplib..Ystep1var&suffix._tr &tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- drop _NAME_;
- run;
- /****************************************/
- /* Sort by Indicator and latest quarter */
- /****************************************/
- proc sort Data=&tmplib..YQstep1var&suffix._tr;
- by Indicator descending &latestq;
- run;
- /************************************/
- /* Choose top n rows by indicators */
- /************************************/
- %global n;
- %let n=⊤
- data &tmplib..YQstep1var&suffix._tr_top;
- set &tmplib..YQstep1var&suffix._tr;
- count + 1;
- by Indicator;
- if first.Indicator then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_others;
- set &tmplib..YQstep1var&suffix._tr_top;
- if count<=&n then output &tmplib..YQstep1var&suffix._tr_topn;
- if count>&n then output &tmplib..YQstep1var&suffix._tr_others;
- run;
- /*
- data &tmplib..YMstep1var&suffix._tr_topn;
- set &tmplib..YMstep1var&suffix._tr_top;
- where count<=&n;
- drop count;
- run;
- /* rows to be aggregated as "others" *//*
- data &tmplib..YMstep1var&suffix._tr_others;
- set &tmplib..YMstep1var&suffix._tr_top;
- where count>&n;
- drop count;
- run;*/
- proc means data=&tmplib..YQstep1var&suffix._tr_others noprint;
- class Indicator;
- var &firsty--&latestq;
- output out=&tmplib..YQstep1var&suffix._tr_othersagg sum=;
- run;
- data &tmplib..YQstep1var&suffix._tr_othersagg;
- set &tmplib..YQstep1var&suffix._tr_othersagg;
- where Indicator ne "";
- drop _TYPE_ _FREQ_;
- &variable="&val";
- &variable2="others";
- run;
- data &tmplib..YQstep1var&suffix._tr_final;
- set &tmplib..YQstep1var&suffix._tr_topn &tmplib..YQstep1var&suffix._tr_othersagg;
- drop count;
- run;
- /* Fancy sorting */
- data &tmplib..YQstep1var&suffix._tr_final;
- set &tmplib..YQstep1var&suffix._tr_final;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- if &variable2 ne "others" then sort2=1;
- if &variable2 = "others" then sort2=2;
- run;
- proc sort Data=&tmplib..YQstep1var&suffix._tr_final;
- by sort1 sort2 descending &latestq;
- run;
- data &tmplib..YQstep1var&suffix._tr_final;
- set &tmplib..YQstep1var&suffix._tr_final;
- drop sort1 sort2;
- run;
- /******************/
- /* BorderLocation */
- /******************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=BorderLocation;
- %let suffix=BL;
- proc sort data=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /****************************/
- /* Quarterly with Yearly */
- /****************************/
- proc sort Data=&tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YQstep1var&suffix._tr;
- merge &tmplib..Ystep1var&suffix._tr &tmplib..Qstep1var&suffix._tr;
- by Indicator &variable2;
- drop _NAME_;
- run;
- /****************************************/
- /* Sort by Indicator and latest quarter */
- /****************************************/
- /********************/
- /* Fancy sorting */
- /********************/
- data &tmplib..YQstep1var&suffix._tr;
- set &tmplib..YQstep1var&suffix._tr;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- run;
- proc sort Data=&tmplib..YQstep1var&suffix._tr;
- by sort1 descending &latestq;
- run;
- data &tmplib..YQstep1var&suffix._tr;
- set &tmplib..YQstep1var&suffix._tr;
- drop sort1;
- run;
- /********************************************/
- /* BorderLocation by top n MemberStates */
- /********************************************/
- /* Choose 3rd dividing variable => ok */
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- proc sort data=&tmplib..Ystep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- run;
- proc sort data=&tmplib..Qstep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YQstep1var&suffix._&suffix2._tr;
- retain &variable;
- merge &tmplib..Ystep1var&suffix._&suffix2._tr &tmplib..Qstep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- run;
- /****************************************/
- /* Sort by Indicator and latest quarter */
- /****************************************/
- proc sort Data=&tmplib..YQstep1var&suffix._&suffix2._tr ;
- by Indicator &variable2 descending &latestq;
- run;
- /************************************/
- /* Choose top n rows by indicators */
- /************************************/
- %global n;
- %let n=⊤
- data &tmplib..YQstep1var&suffix._&suffix2._tr_top;
- set &tmplib..YQstep1var&suffix._&suffix2._tr;
- count + 1;
- by Indicator &variable2;
- if first.Indicator or first.&variable2 then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YQstep1var&suffix._&suffix2._tr_topn(drop=count) &tmplib..YQstep1var&suffix._&suffix2._tr_others(drop=count);
- set &tmplib..YQstep1var&suffix._&suffix2._tr_top(drop=_NAME_);
- if count<=&n then output &tmplib..YQstep1var&suffix._&suffix2._tr_topn;
- if count>&n then output &tmplib..YQstep1var&suffix._&suffix2._tr_others;
- run;
- proc means data=&tmplib..YQstep1var&suffix._&suffix2._tr_others noprint;
- class Indicator &variable2;
- var &firsty--&latestq;
- output out=&tmplib..YQstep1var&suffix._&suffix2._tr_othersagg sum=;
- run;
- data &tmplib..YQstep1var&suffix._&suffix2._tr_othersagg;
- set &tmplib..YQstep1var&suffix._&suffix2._tr_othersagg;
- where Indicator ne "" and &variable2 ne "";
- drop _TYPE_ _FREQ_;
- &variable="&val";
- &variable3="otherMS";
- run;
- data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
- set &tmplib..YQstep1var&suffix._&suffix2._tr_topn &tmplib..YQstep1var&suffix._&suffix2._tr_othersagg;
- run;
- /* Fancy sorting */
- data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
- set &tmplib..YQstep1var&suffix._&suffix2._tr_final;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- if &variable3 ne "otherMS" then sort2=1;
- if &variable3 = "otherMS" then sort2=2;
- run;
- proc sort Data=&tmplib..YQstep1var&suffix._&suffix2._tr_final;
- by sort1 &variable2 sort2 descending &latestq;
- run;
- data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
- set &tmplib..YQstep1var&suffix._&suffix2._tr_final;
- drop sort1 sort2 count;
- run;
- /************************************************/
- /* Tables of years and quarters are ready!!!! */
- /************************************************/
- /********************************/
- /* */
- /* Monthly with Yearly */
- /* */
- /********************************/
- %lm;
- proc sort data=&tmplib..Ystep1var_tr;
- by Indicator;
- run;
- proc sort data=&tmplib..Mstep1var_tr;
- by Indicator;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YMstep1var_tr;
- merge &tmplib..Ystep1var_tr &tmplib..Mstep1var_tr;
- by Indicator;
- run;
- data &tmplib..YMstep1var_tr;
- set &tmplib..YMstep1var_tr;
- &variable="&val";
- run;
- /********************/
- /* Fancy sorting */
- /********************/
- data &tmplib..YMstep1var_tr;
- set &tmplib..YMstep1var_tr;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- run;
- proc sort Data=&tmplib..YMstep1var_tr;
- by sort1 descending &latestm;
- run;
- data &tmplib..YMstep1var_tr;
- set &tmplib..YMstep1var_tr;
- drop sort1;
- run;
- /*********************/
- /* Reporting_country */
- /*********************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=reporting_country_label;
- %let suffix=RC;
- proc sort data=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- run;
- proc sort Data=&tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YMstep1var&suffix._tr;
- merge &tmplib..Ystep1var&suffix._tr &tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- drop _NAME_;
- run;
- data &tmplib..YMshares_tr;
- retain &variable;
- merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
- by Indicator;
- run;
- data &tmplib..YMshares_tr;
- set &tmplib..YMshares_tr;
- &variable="&val";
- run;
- /****************************************/
- /* Sort by Indicator and latest month */
- /****************************************/
- %lm;
- proc sort Data=&tmplib..YMstep1var&suffix._tr;
- by Indicator descending &latestm;
- run;
- /************************************/
- /* Choose top n rows by indicators */
- /************************************/
- %global n;
- %let n=⊤
- data &tmplib..YMstep1var&suffix._tr_top;
- set &tmplib..YMstep1var&suffix._tr;
- count + 1;
- by Indicator;
- if first.Indicator then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_others;
- set &tmplib..YMstep1var&suffix._tr_top;
- if count<=&n then output &tmplib..YMstep1var&suffix._tr_topn;
- if count>&n then output &tmplib..YMstep1var&suffix._tr_others;
- run;
- /*
- data &tmplib..YMstep1var&suffix._tr_topn;
- set &tmplib..YMstep1var&suffix._tr_top;
- where count<=&n;
- drop count;
- run;
- /* rows to be aggregated as "others" *//*
- data &tmplib..YMstep1var&suffix._tr_others;
- set &tmplib..YMstep1var&suffix._tr_top;
- where count>&n;
- drop count;
- run;*/
- proc means data=&tmplib..YMstep1var&suffix._tr_others noprint;
- class Indicator;
- var &firsty--&latestm;
- output out=&tmplib..YMstep1var&suffix._tr_othersagg sum=;
- run;
- data &tmplib..YMstep1var&suffix._tr_othersagg;
- set &tmplib..YMstep1var&suffix._tr_othersagg;
- where Indicator ne "";
- drop _TYPE_ _FREQ_;
- &variable="&val";
- &variable2="others";
- run;
- data &tmplib..YMstep1var&suffix._tr_final;
- set &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_othersagg;
- drop count;
- run;
- /* Fancy sorting */
- data &tmplib..YMstep1var&suffix._tr_final;
- set &tmplib..YMstep1var&suffix._tr_final;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- if &variable2 ne "others" then sort2=1;
- if &variable2 = "others" then sort2=2;
- run;
- proc sort Data=&tmplib..YMstep1var&suffix._tr_final;
- by sort1 sort2 descending &latestm;
- run;
- data &tmplib..YMstep1var&suffix._tr_final;
- set &tmplib..YMstep1var&suffix._tr_final;
- drop sort1 sort2;
- run;
- /************************/
- /* KeyBorderSection */
- /************************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- proc sort data=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- run;
- proc sort Data=&tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YMstep1var&suffix._tr;
- merge &tmplib..Ystep1var&suffix._tr &tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- drop _NAME_;
- run;
- data &tmplib..YMshares_tr;
- retain &variable;
- merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
- by Indicator;
- run;
- data &tmplib..YMshares_tr;
- set &tmplib..YMshares_tr;
- &variable="&val";
- run;
- /****************************************/
- /* Sort by Indicator and latest month */
- /****************************************/
- proc sort Data=&tmplib..YMstep1var&suffix._tr;
- by Indicator descending &latestm;
- run;
- /************************************/
- /* Choose top n rows by indicators */
- /************************************/
- %global n;
- %let n=⊤
- data &tmplib..YMstep1var&suffix._tr_top;
- set &tmplib..YMstep1var&suffix._tr;
- count + 1;
- by Indicator;
- if first.Indicator then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_others;
- set &tmplib..YMstep1var&suffix._tr_top;
- if count<=&n then output &tmplib..YMstep1var&suffix._tr_topn;
- if count>&n then output &tmplib..YMstep1var&suffix._tr_others;
- run;
- /*
- data &tmplib..YMstep1var&suffix._tr_topn;
- set &tmplib..YMstep1var&suffix._tr_top;
- where count<=&n;
- drop count;
- run;
- /* rows to be aggregated as "others" *//*
- data &tmplib..YMstep1var&suffix._tr_others;
- set &tmplib..YMstep1var&suffix._tr_top;
- where count>&n;
- drop count;
- run;*/
- proc means data=&tmplib..YMstep1var&suffix._tr_others noprint;
- class Indicator;
- var &firsty--&latestm;
- output out=&tmplib..YMstep1var&suffix._tr_othersagg sum=;
- run;
- data &tmplib..YMstep1var&suffix._tr_othersagg;
- set &tmplib..YMstep1var&suffix._tr_othersagg;
- where Indicator ne "";
- drop _TYPE_ _FREQ_;
- &variable="&val";
- &variable2="others";
- run;
- data &tmplib..YMstep1var&suffix._tr_final;
- set &tmplib..YMstep1var&suffix._tr_topn &tmplib..YMstep1var&suffix._tr_othersagg;
- drop count;
- run;
- /* Fancy sorting */
- data &tmplib..YMstep1var&suffix._tr_final;
- set &tmplib..YMstep1var&suffix._tr_final;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- if &variable2 ne "others" then sort2=1;
- if &variable2 = "others" then sort2=2;
- run;
- proc sort Data=&tmplib..YMstep1var&suffix._tr_final;
- by sort1 sort2 descending &latestm;
- run;
- data &tmplib..YMstep1var&suffix._tr_final;
- set &tmplib..YMstep1var&suffix._tr_final;
- drop sort1 sort2;
- run;
- /******************/
- /* BorderLocation */
- /******************/
- /* Choose 2nd dividing variable => ok */
- %let variable2=BorderLocation;
- %let suffix=BL;
- proc sort data=&tmplib..Ystep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /********************************/
- /* Monthly with Yearly */
- /********************************/
- proc sort Data=&tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YMstep1var&suffix._tr;
- merge &tmplib..Ystep1var&suffix._tr &tmplib..Mstep1var&suffix._tr;
- by Indicator &variable2;
- drop _NAME_;
- run;
- data &tmplib..YMshares_tr;
- retain &variable;
- merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
- by Indicator;
- run;
- /****************************************/
- /* Sort by Indicator and latest month */
- /****************************************/
- proc sort Data=&tmplib..YMstep1var&suffix._tr;
- by Indicator descending &latestm;
- run;
- /********************************/
- /* Border location - no top 5 */
- /********************************/
- /* Fancy sorting */
- data &tmplib..YMstep1var&suffix._tr;
- set &tmplib..YMstep1var&suffix._tr;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- run;
- proc sort Data=&tmplib..YMstep1var&suffix._tr;
- by sort1 descending &latestm;
- run;
- data &tmplib..YMstep1var&suffix._tr;
- retain &variable &variable2;
- set &tmplib..YMstep1var&suffix._tr;
- drop sort1;
- run;
- /********************************************/
- /* BorderLocation by top n MemberStates */
- /********************************************/
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- proc sort data=&tmplib..Ystep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- run;
- proc sort data=&tmplib..Mstep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- run;
- /*******************/
- /* Join the tables */
- /*******************/
- data &tmplib..YMstep1var&suffix._&suffix2._tr;
- retain &variable;
- merge &tmplib..Ystep1var&suffix._&suffix2._tr &tmplib..Mstep1var&suffix._&suffix2._tr;
- by Indicator &variable2 &variable3;
- run;
- /****************************************/
- /* Sort by Indicator and latest quarter */
- /****************************************/
- proc sort Data=&tmplib..YMstep1var&suffix._&suffix2._tr ;
- by Indicator &variable2 descending &latestm;
- run;
- /************************************/
- /* Choose top n rows by indicators */
- /************************************/
- %global n;
- %let n=⊤
- data &tmplib..YMstep1var&suffix._&suffix2._tr_top;
- set &tmplib..YMstep1var&suffix._&suffix2._tr;
- count + 1;
- by Indicator &variable2;
- if first.Indicator or first.&variable2 then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YMstep1var&suffix._&suffix2._tr_topn(drop=count) &tmplib..YMstep1var&suffix._&suffix2._tr_others(drop=count);
- set &tmplib..YMstep1var&suffix._&suffix2._tr_top(drop=_NAME_);
- if count<=&n then output &tmplib..YMstep1var&suffix._&suffix2._tr_topn;
- if count>&n then output &tmplib..YMstep1var&suffix._&suffix2._tr_others;
- run;
- proc means data=&tmplib..YMstep1var&suffix._&suffix2._tr_others noprint;
- class Indicator &variable2;
- var &firsty--&latestm;
- output out=&tmplib..YMstep1var&suffix._&suffix2._tr_othersagg sum=;
- run;
- data &tmplib..YMstep1var&suffix._&suffix2._tr_othersagg;
- set &tmplib..YMstep1var&suffix._&suffix2._tr_othersagg;
- where Indicator ne "" and &variable2 ne "";
- drop _TYPE_ _FREQ_;
- &variable="&val";
- &variable3="other MS";
- run;
- data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
- set &tmplib..YMstep1var&suffix._&suffix2._tr_topn &tmplib..YMstep1var&suffix._&suffix2._tr_othersagg;
- run;
- /* Fancy sorting */
- data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
- set &tmplib..YMstep1var&suffix._&suffix2._tr_final;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- if &variable3 ne "other MS" then sort2=1;
- if &variable3 = "other MS" then sort2=2;
- run;
- proc sort Data=&tmplib..YMstep1var&suffix._&suffix2._tr_final;
- by sort1 &variable2 sort2 descending &latestm;
- run;
- data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
- set &tmplib..YMstep1var&suffix._&suffix2._tr_final;
- drop sort1 sort2 count;
- run;
- /************************************************/
- /* Tables of years and months are ready!!!! */
- /************************************************/
- /************************************************************/
- /* Changing order of variables */
- /* */
- /* BL - Border Location */
- /* BL_RC - Border Location by Reporting Country */
- /* RC - Reporting Country */
- /* KBS - KeyBorderSection */
- /* */
- /* Yearly with Quarterly */
- /* Yearly with Monthly */
- /* */
- /************************************************************/
- /*
- &tmplib..YQstep1var_tr - Indicator
- &tmplib..YQshares_tr - Indicator
- &tmplib..YQstep1varRC_tr_final - Indicator, variable2
- &tmplib..YQstep1varBL_tr_final - Indicator, variable2
- &tmplib..YQstep1varBL_RC_tr_final - Indicator, variable2, variable3
- &tmplib..YQstep1varKBS_tr_final - Indicator, variable2
- &tmplib..YMstep1var_tr - Indicator
- &tmplib..YMshares_tr - Indicator
- &tmplib..YMstep1varRC_tr_final - Indicator, variable2
- &tmplib..YMstep1varBL_tr_final - Indicator, variable2
- &tmplib..YMstep1varBL_RC_tr_final - Indicator, variable2, variable3
- &tmplib..YMstep1varKBS_tr_final - Indicator, variable2
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- %global start;
- %let start=2012;
- %global end;
- %let end=2013;
- %global bq;
- %let bq=2;
- %global eq;
- %let eq=3;
- %global bm;
- %let bm=1;
- %global em;
- %let em=8;
- %global M;
- %let M=M;
- 1 macro for quarters
- 1 macro for months
- data &tmplib..YQstep1var_tr;
- retain Indicator bla v2012Q2 v2012Q3 v2012Q4 v2012 v2013Q1 v2013Q2 v2013Q3 v2013;
- set &tmplib..YQstep1var_tr;
- run;
- =>we can use variables not existing in a dataset.... =>GOOD ;-D
- */
- /********************************/
- /* Changing order of variables */
- /********************************/
- /* * * * * * * * * * */
- /* Quarterly data */
- /* * * * * * * * * * */
- %qtr_order;
- *%put &qorder.;
- data &tmplib..YQstep1var_tr;
- retain &variable Indicator &qorder;
- set &tmplib..YQstep1var_tr;
- run;
- data &tmplib..YQshares_tr;
- retain &variable Indicator &qorder;
- set &tmplib..YQshares_tr;
- run;
- /********************/
- /* Fancy sorting */
- /********************/
- data &tmplib..YQshares_tr;
- set &tmplib..YQshares_tr;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- run;
- proc sort data=&tmplib..YQshares_tr;
- by sort1;
- run;
- data &tmplib..YQshares_tr;
- set &tmplib..YQshares_tr;
- drop sort1;
- run;
- /* Choose 2nd dividing variable => ok */
- %let variable2=reporting_country_label;
- %let suffix=RC;
- data &tmplib..YQstep1var&suffix._tr_final;
- retain &variable Indicator &variable2 &qorder;
- set &tmplib..YQstep1var&suffix._tr_final;
- run;
- /* Choose 2nd dividing variable => ok */
- %let variable2=BorderLocation;
- %let suffix=BL;
- data &tmplib..YQstep1var&suffix._tr_Final;
- retain &variable Indicator &variable2 &qorder;
- set &tmplib..YQstep1var&suffix._tr_Final;
- run;
- /* Choose 3rd dividing variable => ok */
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- data &tmplib..YQstep1var&suffix._&suffix2._tr_final;
- retain &variable Indicator &variable2 &variable3 &qorder;
- set &tmplib..YQstep1var&suffix._&suffix2._tr_final;
- run;
- /* Choose 2nd dividing variable => ok */
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- data &tmplib..YQstep1var&suffix._tr_final;
- retain &variable Indicator &variable2 &qorder;
- set &tmplib..YQstep1var&suffix._tr_final;
- run;
- /* * * * * * * * * * */
- /* Monthly data */
- /* * * * * * * * * * */
- %mth_order;
- *%put &morder.;
- data &tmplib..YMstep1var_tr;
- retain &variable Indicator &morder;
- set &tmplib..YMstep1var_tr;
- run;
- data &tmplib..YMshares_tr;
- merge &tmplib..Yshares_tr &tmplib..Mshares_tr;
- by Indicator;
- &variable="&val";
- run;
- data &tmplib..YMshares_tr;
- retain &variable Indicator &morder;
- set &tmplib..YMshares_tr;
- run;
- /********************/
- /* Fancy sorting */
- /********************/
- data &tmplib..YMshares_tr;
- set &tmplib..YMshares_tr;
- if Indicator="IBC-1A" then sort1=1;
- if Indicator="ILL-3" then sort1=2;
- if Indicator="REF-4" then sort1=3;
- if Indicator="ASY-5" then sort1=4;
- if Indicator="RET-7A" then sort1=5;
- if Indicator="RET-7B" then sort1=6;
- if Indicator="FAL-6" then sort1=7;
- if Indicator="FAC-2" then sort1=8;
- if Indicator="IBC-1B" then sort1=9;
- run;
- proc sort data=&tmplib..YMshares_tr;
- by sort1;
- run;
- data &tmplib..YMshares_tr;
- set &tmplib..YMshares_tr;
- drop sort1;
- run;
- /* Choose 2nd dividing variable => ok */
- %let variable2=reporting_country_label;
- %let suffix=RC;
- data &tmplib..YMstep1var&suffix._tr_final;
- retain &variable Indicator &variable2 &morder;
- set &tmplib..YMstep1var&suffix._tr_final;
- drop count;
- run;
- /* Choose 2nd dividing variable => ok */
- %let variable2=BorderLocation;
- %let suffix=BL;
- data &tmplib..YMstep1var&suffix._tr_final;
- retain &variable Indicator &variable2 &morder;
- set &tmplib..YMstep1var&suffix._tr;
- run;
- /* Choose 3rd dividing variable => ok */
- %let variable3=reporting_country_label;
- %let suffix2=RC;
- data &tmplib..YMstep1var&suffix._&suffix2._tr_final;
- retain &variable Indicator &variable2 &variable3 &morder;
- set &tmplib..YMstep1var&suffix._&suffix2._tr_final;
- run;
- /* Choose 2nd dividing variable => ok */
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- data &tmplib..YMstep1var&suffix._tr_final;
- retain &variable Indicator &variable2 &morder;
- set &tmplib..YMstep1var&suffix._tr_final;
- drop count;
- run;
- /*********************************/
- /*Member States by BorderLocation*/
- /*********************************/
- %let variable=reporting_country_label;
- %let ind="IBC-1A";
- /*Yearly*/
- proc sql noprint;
- create table &tmplib..Ystep3var as
- select distinct &variable, YEAR,Indicator, sum(Total) as sum_Total
- from &inlib..&set2.
- where YEAR in (&yrs) and Indicator=&ind.
- group by &variable, YEAR, Indicator
- order by &variable, Indicator, YEAR;
- quit;
- proc transpose data=&tmplib..Ystep3var prefix=v
- out=&tmplib..Ystep3var_tr;
- by &variable Indicator;
- id YEAR;
- var sum_Total;
- run;
- /*Monthly*/
- proc sql noprint;
- create table &tmplib..Mstep3var as
- select distinct &variable, YYYYMM,Indicator, sum(Total) as sum_Total
- from &inlib..&set2.
- where YYYYMM in (&mths) and Indicator=&ind.
- group by &variable, YYYYMM, Indicator
- order by &variable, Indicator, YYYYMM;
- quit;
- data &tmplib..Mstep3var;
- set &tmplib..Mstep3var;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- if sum_Total=. then sum_Total=0;
- run;
- proc transpose data=&tmplib..Mstep3var prefix=v
- out=&tmplib..Mstep3var_tr;
- by &variable Indicator;
- id YYYYMM;
- var sum_Total;
- run;
- /*Merge*/
- proc sort data=&tmplib..Ystep3var_tr;
- by &variable;
- run;
- proc sort data=&tmplib..Mstep3var_tr;
- by &variable;
- run;
- data &tmplib..YMstep3var_tr;
- retain &variable Indicator &morder;
- merge &tmplib..Ystep3var_tr &tmplib..Mstep3var_tr;
- by &variable;
- run;
- proc sort data=&tmplib..YMstep3var_tr;
- by descending &latestm;
- run;
- /********************************************************************************/
- /* 3 tables - 1 2 3 */
- /* - border type (all, land, sea) */
- /* - top 10 nationalities + others (aggregated) */
- /* - Totals by border types: all borders, land borders, sea borders etc */
- /* Each - monthly with yearly */
- /********************************************************************************/
- %let variable=Nat_pers_label;
- %let ind="IBC-1A";
- /* All borders */
- /*Yearly*/
- proc sql noprint;
- create table &tmplib..Ystep4var as
- select distinct &variable, YEAR,sum(Total) as sum_Total
- from &inlib..&set2.
- where YEAR in (&yrs) and Indicator in (&ind.)
- group by &variable, YEAR
- order by &variable, YEAR;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Ystep4var prefix=v
- out=&tmplib..Ystep4var_tr;
- by &variable;
- id YEAR;
- var sum_Total;
- run;
- /* Monthly */
- proc sql noprint;
- create table &tmplib..Mstep4var as
- select distinct &variable, YYYYMM,sum(Total) as sum_Total
- from &inlib..&set2.
- where YYYYMM in (&mths) and Indicator in (&ind.)
- group by &variable, YYYYMM
- order by &variable, YYYYMM;
- quit;
- data &tmplib..Mstep4var;
- set &tmplib..Mstep4var;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Mstep4var prefix=v
- out=&tmplib..Mstep4var_tr;
- by &variable;
- id YYYYMM;
- var sum_Total;
- run;
- /* Merge yearly with monthly */
- data &tmplib..YMstep4var_tr;
- drop _NAME_;
- merge &tmplib..Ystep4var_tr &tmplib..Mstep4var_tr;
- by &variable;
- run;
- /* Sort by latest month, then aggregate */
- proc sort Data=&tmplib..YMstep4var_tr;
- by descending &latestm;
- run;
- /* Top n=10 */
- %let n=10;
- /* Number rows in sorted dataset */
- data &tmplib..YMstep4var_tr;
- set &tmplib..YMstep4var_tr;
- count=_N_;
- run;
- /* Split top n rows */
- data &tmplib..YMstep4var_tr_topn &tmplib..YMstep4var_tr_others;
- set &tmplib..YMstep4var_tr;
- if count<=&n then output &tmplib..YMstep4var_tr_topn;
- if count>&n then output &tmplib..YMstep4var_tr_others;
- run;
- proc means data=&tmplib..YMstep4var_tr_others noprint;
- var &firsty--&latestm;
- output out=&tmplib..YMstep4var_tr_othersagg sum=;
- run;
- data &tmplib..YMstep4var_tr_othersagg;
- set &tmplib..YMstep4var_tr_othersagg;
- drop _TYPE_ _FREQ_;
- &variable="others";
- run;
- data &tmplib..YMstep4var_tr_final;
- retain &variable &morder;
- set &tmplib..YMstep4var_tr_topn &tmplib..YMstep4var_tr_othersagg;
- drop count;
- run;
- /********************************************/
- /* By type of border - land, sea, air etc. */
- /* * * * * * * * * * * * * * * * * * * * * * /
- /* Choose 2nd dividing variable => ok */
- /********************************************/
- %let variable2=BorderLocation;
- %let suffix=BL;
- /*Yearly*/
- proc sql noprint;
- create table &tmplib..Ystep4var&suffix. as
- select distinct &variable, &variable2, YEAR,sum(Total) as sum_Total
- from &inlib..&set2.
- where YEAR in (&yrs) and Indicator in (&ind.)
- group by &variable, &variable2, YEAR
- order by &variable, &variable2, YEAR;
- quit;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Ystep4var&suffix. prefix=v
- out=&tmplib..Ystep4var&suffix._tr;
- by &variable &variable2;
- id YEAR;
- var sum_Total;
- run;
- /* Monthly */
- proc sql noprint;
- create table &tmplib..Mstep4var&suffix. as
- select distinct &variable, &variable2, YYYYMM,sum(Total) as sum_Total
- from &inlib..&set2.
- where YYYYMM in (&mths) and Indicator in (&ind.)
- group by &variable, &variable2, YYYYMM
- order by &variable, &variable2, YYYYMM;
- quit;
- data &tmplib..Mstep4var&suffix.;
- set &tmplib..Mstep4var&suffix.;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /* Reshape the dataset so that it looks the way we need.... */
- proc transpose data=&tmplib..Mstep4var&suffix. prefix=v
- out=&tmplib..Mstep4var&suffix._tr;
- by &variable &variable2;
- id YYYYMM;
- var sum_Total;
- run;
- /* Merge yearly with monthly */
- data &tmplib..YMstep4var&suffix._tr;
- retain &variable &variable2;
- drop _NAME_;
- merge &tmplib..Ystep4var&suffix._tr &tmplib..Mstep4var&suffix._tr;
- by &variable &variable2;
- run;
- /* Sort by latest month, then aggregate */
- proc sort Data=&tmplib..YMstep4var&suffix._tr;
- by &variable2 descending &latestm;
- run;
- /* Top n=10 */
- %let n=10;
- data &tmplib..YMstep4var&suffix._tr_top;
- set &tmplib..YMstep4var&suffix._tr;
- count + 1;
- by &variable2;
- if first.&variable2. then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YMstep4var&suffix._tr_topn &tmplib..YMstep4var&suffix._tr_others;
- set &tmplib..YMstep4var&suffix._tr_top;
- if count<=&n then output &tmplib..YMstep4var&suffix._tr_topn;
- if count>&n then output &tmplib..YMstep4var&suffix._tr_others;
- run;
- proc means data=&tmplib..YMstep4var&suffix._tr_others noprint;
- class &variable2;
- var &firsty--&latestm;
- output out=&tmplib..YMstep4var&suffix._tr_othersagg sum=;
- run;
- data &tmplib..YMstep4var&suffix._tr_othersagg;
- set &tmplib..YMstep4var&suffix._tr_othersagg;
- drop _TYPE_ _FREQ_;
- &variable="others";
- run;
- data &tmplib..YMstep4var&suffix._tr_final;
- retain &variable &variable2 &morder;
- set &tmplib..YMstep4var&suffix._tr_topn &tmplib..YMstep4var&suffix._tr_othersagg;
- where &variable2 ne "" and &variable2 ne "Not specified";
- run;
- /********************/
- /* Fancy sorting */
- /********************/
- data &tmplib..YMstep4var&suffix._tr_final;
- set &tmplib..YMstep4var&suffix._tr_final;
- if &variable ne "others" then sort2=1;
- if &variable = "others" then sort2=2;
- drop count;
- run;
- proc sort data=&tmplib..YMstep4var&suffix._tr_final out=&tmplib..YMstep4var&suffix._tr_final(drop=sort2);
- by &variable2 sort2 descending &latestm;
- run;
- /********************************************************/
- /* Replace missing values with 0 - final tables only */
- /********************************************************/
- data &tmplib..YQstep1var_tr(drop=i);
- set &tmplib..YQstep1var_tr;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YQstep1varBL_tr(drop=i);
- set &tmplib..YQstep1varBL_tr;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YQstep1varBL_RC_tr_final(drop=i);
- set &tmplib..YQstep1varBL_RC_tr_final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YQstep1varRC_tr_Final(drop=i);
- set &tmplib..YQstep1varRC_tr_Final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YQstep1varKBS_tr_Final(drop=i);
- set &tmplib..YQstep1varKBS_tr_Final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep1var_tr(drop=i);
- set &tmplib..YMstep1var_tr;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep1varBL_tr(drop=i);
- set &tmplib..YMstep1varBL_tr;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep1varBL_RC_tr_final(drop=i);
- set &tmplib..YMstep1varBL_RC_tr_final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep1varRC_tr_Final(drop=i);
- set &tmplib..YMstep1varRC_tr_Final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep1varKBS_tr_Final(drop=i);
- set &tmplib..YMstep1varKBS_tr_Final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep3var_tr(drop=i);
- set &tmplib..YMstep3var_tr;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep4var_tr_final(drop=i);
- set &tmplib..YMstep4var_tr_final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- data &tmplib..YMstep4varBL_tr_final(drop=i);
- set &tmplib..YMstep4varBL_tr_final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- drop _NAME_;
- run;
- /********************************/
- /* % change - YY, QQ and MM */
- /********************************/
- /*
- Tables to update:
- -Quarterly
- YQstep1var_tr
- YQstep1varBL_tr
- YQstep1varBL_RC_tr_Final
- YQstep1varRC_tr_Final
- YQstep1varKBS_tr_Final
- -Monthly
- YMstep1var_tr
- YMstep1varBL_tr
- YMstep1varBL_RC_tr_Final
- YMstep1varRC_tr_Final
- YMstep1varKBS_tr_Final
- YMstep3var_tr
- YMstep4var_tr_final
- YMstep4varBL_tr_final
- -Variables
- &mt1
- &mt2
- &mt3
- */
- /*
- data &tmplib..YMstep4var&suffix._tr_final;
- set &tmplib..YMstep4var&suffix._tr_final;
- run;
- %let start=%eval(&mt1-&step);
- %global end;
- %let end=&mt1;
- *%put &start.;
- *%put &end.;
- %global Q;
- %let Q=Q;
- %let v=v;
- %global latestq;
- %let latestq=&v&end&Q&eq;
- %let v=v;
- %let firsty=&v&start;
- created in lm macro in FRAN_1_macros
- &prevm
- */
- /*
- yychange_q=[(2013Q1+...+2013Q3)-(2012Q1+...+2012Q3)]/(2012Q1+...+2012Q3)
- yychange_m=[(2013M01+...+2013M11)-(2012M01+...+2012M11)]/(2012M01+...+2012M11)
- qqchange=(2013Q3-2012Q3)/2012Q3
- qqchange2=(2013Q3-2013Q2)/2013Q2
- mmchange=(2013M11-2012M11)/2012M11
- mmchange2=(2013M11-2013M10)/(2013M10)
- */
- %previous;
- *%put _all_;
- /************************/
- /* Calculate % changes */
- /************************/
- /*
- yychange_q=[(2013Q1+...+2013Q3)-(2012Q1+...+2012Q3)]/(2012Q1+...+2012Q3)
- yychange_m=[(2013M01+...+2013M11)-(2012M01+...+2012M11)]/(2012M01+...+2012M11)
- qqchange=(2013Q3-2012Q3)/2012Q3
- qqchange2=(2013Q3-2013Q2)/2013Q2
- mmchange=(2013M11-2012M11)/2012M11
- mmchange2=(2013M11-2013M10)/(2013M10)
- */
- /*
- yychange_q:
- latestq_ty
- firstq_ty
- latestq_py
- firstq_py
- qqchange:
- latestq_ty
- latestq_py
- qqchange2:
- latestq_ty
- prevq
- yychange_q=[(2013Q1+...+2013Q3)-(2012Q1+...+2012Q3)]/(2012Q1+...+2012Q3)
- qqchange=(2013Q3-2012Q3)/2012Q3
- qqchange2=(2013Q3-2013Q2)/2013Q2
- */
- /* Quarterly */
- data &tmplib..YQstep1var_tr;
- set &tmplib..YQstep1var_tr;
- qqchange2=(&latestq_ty-&prevq)/&prevq;
- qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
- yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
- format qqchange2 qqchange yychange percentn10.1;
- run;
- data &tmplib..YQstep1varBL_tr;
- set &tmplib..YQstep1varBL_tr;
- qqchange2=(&latestq_ty-&prevq)/&prevq;
- qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
- yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
- format qqchange2 qqchange yychange percentn10.1;
- run;
- data &tmplib..YQstep1varBL_RC_tr_Final;
- set &tmplib..YQstep1varBL_RC_tr_Final;
- qqchange2=(&latestq_ty-&prevq)/&prevq;
- qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
- yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
- format qqchange2 qqchange yychange percentn10.1;
- run;
- data &tmplib..YQstep1varRC_tr_Final;
- set &tmplib..YQstep1varRC_tr_Final;
- qqchange2=(&latestq_ty-&prevq)/&prevq;
- qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
- yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
- format qqchange2 qqchange yychange percentn10.1;
- run;
- data &tmplib..YQstep1varKBS_tr_Final;
- set &tmplib..YQstep1varKBS_tr_Final;
- qqchange2=(&latestq_ty-&prevq)/&prevq;
- qqchange=(&latestq_ty-&latestq_py)/&latestq_py;
- yychange=(sum(&firstq_ty--&latestq_ty)-sum(&firstq_py--&latestq_py))/sum(&firstq_py--&latestq_py);
- format qqchange2 qqchange yychange percentn10.1;
- run;
- /*
- yychange_m:
- latestm_ty
- firstm_ty
- latestm_py
- firstm_py
- mmchange:
- latestm_ty
- latestm_py
- mmchange2:
- latestm_ty
- prevm
- yychange_m=[(2013M01+...+2013M11)-(2012M01+...+2012M11)]/(2012M01+...+2012M11)
- mmchange=(2013M11-2012M11)/2012M11
- mmchange2=(2013M11-2013M10)/(2013M10)
- */
- /* Monthly */
- data &tmplib..YMstep1var_tr;
- set &tmplib..YMstep1var_tr;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- data &tmplib..YMstep1varBL_tr;
- set &tmplib..YMstep1varBL_tr;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- data &tmplib..YMstep1varBL_RC_tr_Final;
- set &tmplib..YMstep1varBL_RC_tr_Final;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- data &tmplib..YMstep1varRC_tr_Final;
- set &tmplib..YMstep1varRC_tr_Final;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- data &tmplib..YMstep1varKBS_tr_Final;
- set &tmplib..YMstep1varKBS_tr_Final;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- data &tmplib..YMstep3var_tr;
- set &tmplib..YMstep3var_tr;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- data &tmplib..YMstep4var_tr_final;
- set &tmplib..YMstep4var_tr_final;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- data &tmplib..YMstep4varBL_tr_final;
- set &tmplib..YMstep4varBL_tr_final;
- mmchange2=(&latestm_ty-&prevm)/&prevm;
- mmchange=(&latestm_ty-&latestm_py)/&latestm_py;
- yychange=(sum(&firstm_ty--&latestm_ty)-sum(&firstm_py--&latestm_py))/sum(&firstm_py--&latestm_py);
- format mmchange2 mmchange yychange percentn10.1;
- run;
- /****************************************************************************************************/
- /*
- ___________.__ .__ .__ __ .__ ___________ _______ ________ ._._._.
- \__ ___/| |__ |__| ______ |__| ______ _/ |_| |__ ____ \_ _____/ \ \ \______ \| | | |
- | | | | \| |/ ___/ | |/ ___/ \ __\ | \_/ __ \ | __)_ / | \ | | \ | | |
- | | | Y \ |\___ \ | |\___ \ | | | Y \ ___/ | \/ | \| ` \|\|\|
- |____| |___| /__/____ > |__/____ > |__| |___| /\___ > /_______ /\____|__ /_______ /_____
- \/ \/ \/ \/ \/ \/ \/ \/\/\/\/
- */
- /*****************************************************************************************************/
- /*
- _____ _ _ _
- / __ \ | | | | | |
- | / \/_ __ ___ __ _| |_ ___ __| | | |__ _ _
- | | | '__/ _ \/ _` | __/ _ \/ _` | | '_ \| | | |
- | \__/\ | | __/ (_| | || __/ (_| | | |_) | |_| |
- \____/_| \___|\__,_|\__\___|\__,_| |_.__/ \__, |
- __/ |
- |___/
- _ __ _ _____ _ _ _
- | | / / | | | _ | | | | (_)
- | |/ / ___ _ __ _ __ ___| | | | | | |___ _________ _____| | ___
- | \ / _ \| '__| '_ \ / _ \ | | | | | / __|_ / _ \ \ /\ / / __| |/ / |
- | |\ \ (_) | | | | | | __/ | \ \_/ / \__ \/ / __/\ V V /\__ \ <| |
- \_| \_/\___/|_| |_| |_|\___|_| \___/|_|___/___\___| \_/\_/ |___/_|\_\_|
- */
- *%put _all_;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement