Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- ______ _____ _ _ _ _ _
- | ____| __ \ /\ | \ | | | | | | | |
- | |__ | |__) | / \ | \| | | |_ __ _| |__ | | ___ ___
- | __| | _ / / /\ \ | . ` | | __/ _` | '_ \| |/ _ \/ __|
- | | | | \ \ / ____ \| |\ | | || (_| | |_) | | __/\__ \
- |_| |_| \_\/_/ \_\_|_\_| \__\__,_|_.__/|_|\___||___/
- | | (_) | (_) | |
- | |__ _ _ _ _ __ __| |_ ___ __ _| |_ ___ _ __ ___
- | '_ \| | | | | | '_ \ / _` | |/ __/ _` | __/ _ \| '__/ __|
- | |_) | |_| | | | | | | (_| | | (_| (_| | || (_) | | \__ \
- |_.__/ \__, | |_|_| |_|\__,_|_|\___\__,_|\__\___/|_| |___/
- __/ |
- |___/
- */
- /********************************/
- /* */
- /* Other tables from FRAN */
- /* for the sake of reporting */
- /* */
- /********************************/
- /************************************************************/
- /* 5. Yearly, quarterly or monthly data for one indicator */
- /* !!!!!!!!!!!!! */
- /************************************************************/
- /* Put it into another ordered list */
- /* Prompt for selecting number of years back */
- /* Take values from time */
- /********************************************************/
- %macro ind_rename;
- %if &idc=_IBC1A_ %then %let idc2="IBC-1A";
- %if &idc=_ILL3_ %then %let idc2="ILL-3";
- %if &idc=_REF4_ %then %let idc2="REF-4";
- %if &idc=_ASY5_ %then %let idc2="ASY-5";
- %if &idc=_RET7A_ %then %let idc2="RET-7A";
- %if &idc=_RET7B_ %then %let idc2="RET-7B";
- %if &idc=_FAL6_ %then %let idc2="FAL-6";
- %if &idc=_FAC2_ %then %let idc2="FAC-2";
- %if &idc=_IBC1B_ %then %let idc2="IBC-1B";
- %put &idc2.;
- %mend ind_rename;
- /* Nationalities with another variable: member state or border section */
- %macro ind_select;
- %ind_rename;
- /* Totals over years for chosen indicator, nationality and 2nd grouping variable */
- proc sql;
- create table &tmplib..Yots1&suffix&idc as
- select distinct Indicator, &variable, &variable2, YEAR, sum(Total) as sum_Total
- from &inlib..&set2.
- where Indicator=&idc2 and YEAR in (&yrs)
- group by Indicator, YEAR, &variable, &variable2
- order by &variable, &variable2, YEAR;
- quit;
- /* Totals over quarters for chosen indicator, nationality and 2nd grouping variable */
- proc sql;
- create table &tmplib..Qots1&suffix&idc as
- select distinct Indicator, &variable, &variable2, YYYYQ, sum(Total) as sum_Total
- from &inlib..&set2.
- where Indicator=&idc2 and YYYYQ in (&qtrs)
- group by Indicator, YYYYQ, &variable, &variable2
- order by &variable, &variable2, YYYYQ;
- quit;
- /* Totals over months for chosen indicator, nationality and 2nd grouping variable */
- proc sql;
- create table &tmplib..Mots1&suffix&idc as
- select distinct Indicator, &variable, &variable2, YYYYMM, sum(Total) as sum_Total
- from &inlib..&set2.
- where Indicator=&idc2 and YYYYMM in (&mths)
- group by Indicator, YYYYMM, &variable, &variable2
- order by &variable, &variable2, YYYYMM;
- quit;
- /* Rename the months */
- data &tmplib..Mots1&suffix&idc;
- set &tmplib..Mots1&suffix&idc;
- YYYYMM=prxchange('s/ /0/',-1,YYYYMM);
- run;
- /************************************************************/
- /* Reshape the dataset so that it looks the way we need.... */
- /************************************************************/
- /* Yearly */
- proc transpose data=&tmplib..Yots1&suffix&idc prefix=v
- out=&tmplib..Yots1&suffix&idc._tr(drop=_NAME_);
- by Indicator &variable &variable2;
- id YEAR;
- var sum_Total;
- run;
- /* Quarterly */
- proc transpose data=&tmplib..Qots1&suffix&idc prefix=v
- out=&tmplib..Qots1&suffix&idc._tr(drop=_NAME_);
- by Indicator &variable &variable2;
- id YYYYQ;
- var sum_Total;
- run;
- /* Monthly */
- proc transpose data=&tmplib..Mots1&suffix&idc prefix=v
- out=&tmplib..Mots1&suffix&idc._tr(drop=_NAME_);
- by Indicator &variable &variable2;
- id YYYYMM;
- var sum_Total;
- run;
- /********************************************************/
- /* Merge Yearly with Quarterly and Yearly with Monthly */
- /* Reorder variables */
- /********************************************************/
- /* Yearly + Quarterly */
- data &tmplib..YQots1&suffix&idc._tr;
- merge &tmplib..Yots1&suffix&idc._tr &tmplib..Qots1&suffix&idc._tr;
- by Indicator &variable &variable2;
- run;
- /* Yearly + Monthly */
- data &tmplib..YMots1&suffix&idc._tr;
- merge &tmplib..Yots1&suffix&idc._tr &tmplib..Mots1&suffix&idc._tr;
- by Indicator &variable &variable2;
- run;
- /****************************************************************************/
- /* Select top 10 combinations by latest quarter/month, aggregate others */
- /****************************************************************************/
- /*Latest quarter*/
- %global Q;
- %let Q=Q;
- %let v=v;
- %global latestq;
- %let latestq=&v&end&Q&eq;
- %let firsty=&v&start;
- /********************************/
- /* Sort by latest quarter/month */
- /********************************/
- proc sort Data=&tmplib..YQots1&suffix&idc._tr ;
- by descending &latestq;
- run;
- %lm;
- proc sort Data=&tmplib..YMots1&suffix&idc._tr ;
- by descending &latestm;
- run;
- /**********************/
- /* Choose top n rows */
- /**********************/
- data &tmplib..YQots1&suffix&idc._tr;
- set &tmplib..YQots1&suffix&idc._tr;
- count + 1;
- by Indicator;
- if first.Indicator then count = 1;
- run;
- data &tmplib..YMots1&suffix&idc._tr;
- set &tmplib..YMots1&suffix&idc._tr;
- count + 1;
- by Indicator;
- if first.Indicator then count = 1;
- run;
- /* Top n rows */
- data &tmplib..YQots1&suffix&idc._tr_topn &tmplib..YQots1&suffix&idc._tr_others;
- set &tmplib..YQots1&suffix&idc._tr;
- if count<=&n then output &tmplib..YQots1&suffix&idc._tr_topn;
- if count>&n then output &tmplib..YQots1&suffix&idc._tr_others;
- run;
- data &tmplib..YMots1&suffix&idc._tr_topn &tmplib..YMots1&suffix&idc._tr_others;
- set &tmplib..YMots1&suffix&idc._tr;
- if count<=&n then output &tmplib..YMots1&suffix&idc._tr_topn;
- if count>&n then output &tmplib..YMots1&suffix&idc._tr_others;
- run;
- /* Aggregate others */
- proc means data=&tmplib..YQots1&suffix&idc._tr_others noprint;
- class Indicator;
- var &firsty--&latestq;
- output out=&tmplib..YQots1&suffix&idc._tr_othersagg sum=;
- run;
- proc means data=&tmplib..YMots1&suffix&idc._tr_others noprint;
- class Indicator;
- var &firsty--&latestm;
- output out=&tmplib..YMots1&suffix&idc._tr_othersagg sum=;
- run;
- data &tmplib..YQots1&suffix&idc._tr_othersagg;
- set &tmplib..YQots1&suffix&idc._tr_othersagg;
- where Indicator ne "";
- drop _TYPE_ _FREQ_;
- &variable="others";
- &variable2="others";
- run;
- data &tmplib..YMots1&suffix&idc._tr_othersagg;
- set &tmplib..YMots1&suffix&idc._tr_othersagg;
- where Indicator ne "";
- drop _TYPE_ _FREQ_;
- &variable="others";
- &variable2="others";
- run;
- /* Append tables */
- data &tmplib..YQots1&suffix&idc._tr_final;
- set &tmplib..YQots1&suffix&idc._tr_topn &tmplib..YQots1&suffix&idc._tr_othersagg;
- drop count;
- run;
- data &tmplib..YMots1&suffix&idc._tr_final;
- set &tmplib..YMots1&suffix&idc._tr_topn &tmplib..YMots1&suffix&idc._tr_othersagg;
- drop count;
- run;
- /************************/
- /* Reorder variables */
- /************************/
- data &tmplib..YQots1&suffix&idc._tr_final;
- retain Indicator &variable &variable2 &qorder;
- set &tmplib..YQots1&suffix&idc._tr_final;
- run;
- data &tmplib..YMots1&suffix&idc._tr_final;
- retain Indicator &variable &variable2 &morder;
- set &tmplib..YMots1&suffix&idc._tr_final;
- run;
- /*********************************/
- /* Replace missing values with 0 */
- /*********************************/
- /* Yearly with Quarterly */
- data &tmplib..YQots1&suffix&idc._tr_final(drop=i);
- set &tmplib..YQots1&suffix&idc._tr_final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- run;
- /* Yearly with Monthly */
- data &tmplib..YMots1&suffix&idc._tr_final(drop=i);
- set &tmplib..YMots1&suffix&idc._tr_final;
- array testmiss(*) _numeric_;
- do i = 1 to dim(testmiss);
- if testmiss(i)=. then testmiss(i)=0;
- end;
- run;
- %mend ind_select;
- /* Tables to be exported */
- /* */
- /* Select main variable - nat_pers_label from prompt to FRAN_2_time */
- %global variable;
- %let variable=&vari;
- %time_secure;
- /* Final year */
- %global end;
- %let end=&mt1;
- /* Quarters */
- %global bq;
- %let bq=1;
- *%put &qtr1.;
- *%put &bq.;
- %global eq;
- %let eq=&mt2;
- *%put &qtr2.;
- *%put &eq.;
- /* Months */
- %global bm;
- %let bm=1;
- *%put &mth1.;
- *%put &bm.;
- %global em;
- %let em=&mt3;
- *%put &mth2.;
- *%put &em.;
- %years(start=&start,end=&end);
- %quarters(start=&start,end=&end,bq=&bq,eq=&eq);
- %months(start=&start,end=&end,bm=&bm,em=&em);
- %qtr_order;
- %mth_order;
- %global idc;
- %global idc2;
- %global variable2;
- %global suffix;
- /************************/
- /* V1: member states */
- /************************/
- %let variable2=reporting_country_label;
- %let suffix=RC;
- %global n;
- %let n=10;
- /* Run macro ind_select separately per each indicator */
- %let idc=_IBC1A_;
- %ind_select;
- %let idc=_ILL3_;
- %ind_select;
- %let idc=_REF4_;
- %ind_select;
- %let idc=_ASY5_;
- %ind_select;
- %let idc=_RET7A_;
- %ind_select;
- %let idc=_RET7B_;
- %ind_select;
- %let idc=_FAL6_;
- %ind_select;
- %let idc=_FAC2_;
- %ind_select;
- %let idc=_IBC1B_;
- %ind_select;
- /************************/
- /* V2: border section */
- /************************/
- %let variable2=KeyBorderSection;
- %let suffix=KBS;
- %global n;
- %let n=10;
- /* Run macro ind_select separately per each indicator */
- %let idc=_IBC1A_;
- %ind_select;
- %let idc=_ILL3_;
- %ind_select;
- %let idc=_REF4_;
- %ind_select;
- %let idc=_ASY5_;
- %ind_select;
- %let idc=_RET7A_;
- %ind_select;
- %let idc=_RET7B_;
- %ind_select;
- %let idc=_FAL6_;
- %ind_select;
- %let idc=_FAC2_;
- %ind_select;
- %let idc=_IBC1B_;
- %ind_select;
- /****************************************************************************************************/
- /*
- ___________.__ .__ .__ __ .__ ___________ _______ ________ ._._._.
- \__ ___/| |__ |__| ______ |__| ______ _/ |_| |__ ____ \_ _____/ \ \ \______ \| | | |
- | | | | \| |/ ___/ | |/ ___/ \ __\ | \_/ __ \ | __)_ / | \ | | \ | | |
- | | | Y \ |\___ \ | |\___ \ | | | Y \ ___/ | \/ | \| ` \|\|\|
- |____| |___| /__/____ > |__/____ > |__| |___| /\___ > /_______ /\____|__ /_______ /_____
- \/ \/ \/ \/ \/ \/ \/ \/\/\/\/
- */
- /*****************************************************************************************************/
- /*
- _____ _ _ _
- / __ \ | | | | | |
- | / \/_ __ ___ __ _| |_ ___ __| | | |__ _ _
- | | | '__/ _ \/ _` | __/ _ \/ _` | | '_ \| | | |
- | \__/\ | | __/ (_| | || __/ (_| | | |_) | |_| |
- \____/_| \___|\__,_|\__\___|\__,_| |_.__/ \__, |
- __/ |
- |___/
- _ __ _ _____ _ _ _
- | | / / | | | _ | | | | (_)
- | |/ / ___ _ __ _ __ ___| | | | | | |___ _________ _____| | ___
- | \ / _ \| '__| '_ \ / _ \ | | | | | / __|_ / _ \ \ /\ / / __| |/ / |
- | |\ \ (_) | | | | | | __/ | \ \_/ / \__ \/ / __/\ V V /\__ \ <| |
- \_| \_/\___/|_| |_| |_|\___|_| \___/|_|___/___\___| \_/\_/ |___/_|\_\_|
- */
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement