SAS macros for exporting to multiple sheets.

[troopon]

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

[troopon · Accepted solution]

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 &&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

[RichardinOz]

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

[Reeza]

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