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

Hi ,

I am looking for a macro code as i have a variable name city and that variable contains 126 names of unique cities,

and want to export the same data set into excel with condition where city='xyz'

and the excel should contain the name of the same city which is used in the condition.

 

As using the following code for 126 unique cities is not affordable.

 

 

e.g.: proc export
data=WORK.CAPFIRST_ENCOLLEC_MASTER (where=(City='PUNE'))
dbms=xlsx
outfile="\\10.4.1.205\Upload\DAC_FINAL_PUNE.xlsx"
replace;
run;

 

So looking for macro and looping to export using that macro.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Does this work as expected - it should create two workbooks, one for males and one for females?

 

If so, use this as the template for your code. 

 

proc sql;
create table distinct_sex as
select distinct sex from sashelp.class;
quit;

%macro export_sheet(sex=);


proc export data=sashelp.class (where=(sex="&sex")) 
outfile="C:\_localdata\sex_&sex..xlsx" dbms=xlsx replace; run;

%mend;

data _null_;
set distinct_sex;

str = catt('%export_sheet(sex=', sex, ');');

call execute (str);

run;

View solution in original post

32 REPLIES 32
Tom
Super User Tom
Super User

Try using ODS EXCEL instead.  You can tell it to make a separate sheet for each BY group.

Reeza
Super User

@anirudhs wrote:

Hi ,

I am looking for a macro code as i have a variable name city and that variable contains 126 names of unique cities,

and want to export the same data set into excel with condition where city='xyz'

and the excel should contain the name of the same city which is used in the condition.

 

As using the following code for 126 unique cities is not affordable.

 

 

e.g.: proc export
data=WORK.CAPFIRST_ENCOLLEC_MASTER (where=(City='PUNE'))
dbms=xlsx
outfile="\\10.4.1.205\Upload\DAC_FINAL_PUNE.xlsx"
replace;
run;

 

So looking for macro and looping to export using that macro.

 


Are you trying to create a new workbook for each city or a new worksheet for each city?

 

If it's a new worksheet, then use the ODS EXCEL with BY option as indicated by @Tom

If a new workbook, then you would need another option. Rather than a macro, for something this simple, I would recommend Call Execute. 

 

anirudhs
Obsidian | Level 7
yes i want new work book for each city.

but the code and condition is to be written 126 times... is that fisible ????

to write the proc export code 126 times by just changing the city name in each occurrence of export code
Tom
Super User Tom
Super User

You don't really need a macro.  You just need to use code generation.  Since you a driving the code generation from data it is probably easier to do with a data step.  You could use CALL EXECUTE().

data _null_;
  set WORK.CAPFIRST_ENCOLLEC_MASTER ;
  by city;
  if first.city ;
  call execute(catx(' '
,'proc export'
,'data=WORK.CAPFIRST_ENCOLLEC_MASTER dbms=xlsx'
,'outfile=',quote(cats('\\10.4.1.205\Upload\DAC_FINAL_',city,'.xlsx'))
,'replace;'
,'where City=',quote(trim(city)),';'
,'run;'
));
run;
Reeza
Super User
@Tom I think you're missing the PROC EXPORT text portion.
Tom
Super User Tom
Super User
Thanks. Updated.
Reeza
Super User

@anirudhs wrote:
yes i want new work book for each city.

but the code and condition is to be written 126 times... is that fisible ????

to write the proc export code 126 times by just changing the city name in each occurrence of export code

No one suggested you do that. We asked what you were trying to do, so we can provide a better response. 

You can make the code be generated automatically, see @Toms answer. A macro would work but it's more code. 

 

1. First get a list of all citys

2. Write a macro to export

3. Call macro for each city using call execute.

 

Untested below:

 

proc sql;
create table distinct_city as
select distinct city from have;
quit;

%macro export_sheet(city=);


proc export data=&have (where=(city="&city")) 
outfile="C:\_localdata\&City..xlsx" dbms=xlsx replace; run;

%mend;

data _null_;
set distinct_city;

str = catt('%export_sheet(city=', city, ');');

call execute (str);

run;

Personally, I'd use Tom's solution instead. 

anirudhs
Obsidian | Level 7
I will try and test both the codes,
but i would like to say that the data set contains round about 3 lakhs rows of data with recuring of city names with minimum of 5000 rows.

SO i will update once i test both the code.
Thanks
Reeza
Super User

300K rows is trivial. If it can fit in Excel its not big data 🙂

 

 

anirudhs
Obsidian | Level 7
this is the following error i am getting when used the code you have mentioned

WARNING: Apparent symbolic reference WORK not resolved.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Line generated by the CALL EXECUTE routine.
1 + proc export data=&WORK.DAC_3 (where=(city='&city')) outfile="\\10.4.1.205\Upload\DAC_AGRA.xlsx" dbms=xlsx replace;
run;;
_
22
76
ERROR 22-322: Expecting a name.

ERROR 76-322: Syntax error, statement will be ignored.

please update..
anirudhs
Obsidian | Level 7
@Tom getting following errors for all cities when testing your code :


NOTE: Line generated by the CALL EXECUTE routine.
89 + data=WORK.DAC_3 dbms=xlsx outfile= "\\10.4.1.205\Upload\DAC_FINAL_YAMUNANAGAR.xlsx" replace;
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.
Tom
Super User Tom
Super User

@anirudhs wrote:
@Tom getting following errors for all cities when testing your code :


NOTE: Line generated by the CALL EXECUTE routine.
89 + data=WORK.DAC_3 dbms=xlsx outfile= "\\10.4.1.205\Upload\DAC_FINAL_YAMUNANAGAR.xlsx" replace;
____
180

ERROR 180-322: Statement is not valid or it is used out of proper order.

Most likely you have a missing or extra semi-colon.  Would need to see the whole log to debug, but you should be able to debug on your own.  Get the syntax for one proc step right and working and then make sure the data step is generating the same syntax.

anirudhs
Obsidian | Level 7
Yeh found it, and is running but taking too long time to execute.
Tom
Super User Tom
Super User

How many XLSX files are you generating?

Why are you generating so many XLSX files?

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
  • 32 replies
  • 12294 views
  • 10 likes
  • 4 in conversation