BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
troopon
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
troopon
Calcite | Level 5

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

3 REPLIES 3
troopon
Calcite | Level 5

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

RichardinOz
Quartz | Level 8

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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