Saturday, March 15, 2014

Data _null_ instead of Do Loop (SAS)

Avoiding do loop in SAS

Lets suppose I have data set like

Input Dataset:

Country Month Sales
India Jan 100
India Feb 200
US Jan 300
US Feb 500
UK Jan 150
UK Feb 350

I need separate output dataset for all the contries present in Input Dataset with cumulative sales.

Instead of counting countries and using a Do loop.
Create a dataset with distinct countries call a macro inside a datastep.

Function used:
Data _null_ , call symputx(), Call execute.

Try it out yourself then refer to code below. Can leave a feedback/suggestions in the comments.



/*Input dataset*/
data country_sales;
input country $ Month $ sales;
cards;
India Jan 100
India Feb 200
US Jan 300
US Feb 500
UK Jan 150
UK Feb 350
;
run;

/*Sorting data before using by clause for first. & last. */
proc sort data = country_sales;
by country;
run;

/*Creating master data set instead of using Do Loop*/
data country_master;
set country_sales (keep = country);
by country;
if first.country;
run;

/*Macro to create separate dataset for each country*/
%macro cum_sales();
data &macro_var. (keep = country cum_sales);
set country_sales;
cum_sales + sales;
if last.country;
by country;
where upcase(trim(left(country))) = upcase("&macro_var.");
run;
%mend cum_sales;

/*Creating the macro variable and running macro for each country*/
data _null_;
set country_master;
call symputx('macro_var',country,G);
call execute('%cum_sales');
run;

No comments:

Post a Comment