Avoiding do loop in SAS
Lets suppose I have data set like
Input Dataset:
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 ¯o_var. (keep = country cum_sales);
set country_sales;
cum_sales + sales;
if last.country;
by country;
where upcase(trim(left(country))) = upcase("¯o_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;
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 ¯o_var. (keep = country cum_sales);
set country_sales;
cum_sales + sales;
if last.country;
by country;
where upcase(trim(left(country))) = upcase("¯o_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