Hi
I have built the following simple macros for exporting a dataset into sas but creating a different sheet for each city. However it doesn't work because it is searching for city1 city2 instead of the name and therefore returning 0 results and exporting to excel naming the tabs city1 city 2 etc.
Is there something I have missed that I need to do to make this work? Or is there some kind of alternative other than having multiple export statements which i don't want to do.
(There are more than 3 cities by the way, I just shortened this one for the sake of posting)
Cheers
Stephen
%let | city1 | = | Manchester | ; |
%let | city2 | = | Birmingham | ; |
%let | city3 | = | Bristol | ; |
%macro export;
%do i = 1 %to 3;
proc export data = file1 (where = (city = "city&i."))replace
outfile = 'file location.xls'
DBMS = excel;
sheet = "city&i.";
run;
%end;
%mend export;
%export
never mind figured it out although don't completely understand it. More trial and error
%let | city1 | = | Manchester | ; |
%let | city2 | = | Birmingham | ; |
%let | city3 | = | Bristol | ; |
%macro export;
%do i = 1 %to 3;
%put number is &i &¢re&i;
proc export data = file1 (where = (centre_formatted = "&&city&i."))replace
outfile = 'file location.xls'
DBMS = excel;
sheet = "&&city&i.";
run;
%end;
%mend export;
%export
never mind figured it out although don't completely understand it. More trial and error
%let | city1 | = | Manchester | ; |
%let | city2 | = | Birmingham | ; |
%let | city3 | = | Bristol | ; |
%macro export;
%do i = 1 %to 3;
%put number is &i &¢re&i;
proc export data = file1 (where = (centre_formatted = "&&city&i."))replace
outfile = 'file location.xls'
DBMS = excel;
sheet = "&&city&i.";
run;
%end;
%mend export;
%export
Looks like &¢re&i should be &&city&i. (In this case the period after &i is OK but not needed)
When the SAS macro processor encounters multiple "&" it replaces each pair with a single and then evaluates the expression again, repeatedly, until all multiple instances are reduced to one.
Input: &&city&i.
First pass: resolves to &city1 (assuming &i = 1)
Final pass: resolves to Manchester
Richard
Another option is tagsets, though I think it takes longer and the file is bigger. Easier to code if you're in a rush though.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.