SAS macros for exporting to multiple sheets.

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

SAS macros for exporting to multiple sheets.

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

%letcity1=Manchester;
%letcity2=Birmingham;
%letcity3=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


Accepted Solutions
Solution
‎09-19-2013 04:54 AM
Contributor
Posts: 28

Re: SAS macros for exporting to multiple sheets.

never mind figured it out although don't completely understand it. More trial and error

%letcity1=Manchester;
%letcity2=Birmingham;
%letcity3=Bristol;

%macro export;

%do i = 1 %to 3;

%put number is &i &&centre&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

View solution in original post


All Replies
Solution
‎09-19-2013 04:54 AM
Contributor
Posts: 28

Re: SAS macros for exporting to multiple sheets.

never mind figured it out although don't completely understand it. More trial and error

%letcity1=Manchester;
%letcity2=Birmingham;
%letcity3=Bristol;

%macro export;

%do i = 1 %to 3;

%put number is &i &&centre&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

Super Contributor
Posts: 644

Re: SAS macros for exporting to multiple sheets.

Looks like &&centre&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

Super User
Posts: 17,829

Re: SAS macros for exporting to multiple sheets.

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.

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 497 views
  • 0 likes
  • 3 in conversation