DATA Step, Macro, Functions and more

Macro code to export in excel

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Macro code to export in excel

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.

 


Accepted Solutions
Solution
‎04-04-2018 12:06 AM
Super User
Posts: 23,980

Re: Macro code to export in excel

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


All Replies
Super User
Super User
Posts: 8,267

Re: Macro code to export in excel

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

Super User
Posts: 23,980

Re: Macro code to export in excel


@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. 

 

Contributor
Posts: 70

Re: Macro code to export in excel

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
Super User
Super User
Posts: 8,267

Re: Macro code to export in excel

[ Edited ]

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;
Super User
Posts: 23,980

Re: Macro code to export in excel

@Tom I think you're missing the PROC EXPORT text portion.
Super User
Super User
Posts: 8,267

Re: Macro code to export in excel

Thanks. Updated.
Super User
Posts: 23,980

Re: Macro code to export in excel

[ Edited ]

@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. 

Contributor
Posts: 70

Re: Macro code to export in excel

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
Super User
Posts: 23,980

Re: Macro code to export in excel

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

 

 

Contributor
Posts: 70

Re: Macro code to export in excel

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..
Contributor
Posts: 70

Re: Macro code to export in excel

@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.
Super User
Super User
Posts: 8,267

Re: Macro code to export in excel


@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.

Contributor
Posts: 70

Re: Macro code to export in excel

Yeh found it, and is running but taking too long time to execute.
Super User
Super User
Posts: 8,267

Re: Macro code to export in excel

How many XLSX files are you generating?

Why are you generating so many XLSX files?

☑ This topic is solved.

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

Discussion stats
  • 32 replies
  • 264 views
  • 8 likes
  • 4 in conversation