topic Re: Macro code to export in excel in SAS Programming

Macro code to export in excel

anirudhs — Wed, 28 Mar 2018 14:22:25 GMT

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.

Re: Macro code to export in excel

Tom — Wed, 28 Mar 2018 14:35:03 GMT

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

Re: Macro code to export in excel

Reeza — Wed, 28 Mar 2018 14:45:40 GMT

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

Re: Macro code to export in excel

anirudhs — Wed, 28 Mar 2018 16:05:47 GMT

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

Re: Macro code to export in excel

Tom — Wed, 28 Mar 2018 16:41:06 GMT

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;

Re: Macro code to export in excel

Reeza — Wed, 28 Mar 2018 16:22:54 GMT

@Tom I think you're missing the PROC EXPORT text portion.

Re: Macro code to export in excel

Reeza — Wed, 28 Mar 2018 16:28:30 GMT

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

Re: Macro code to export in excel

Tom — Wed, 28 Mar 2018 16:44:15 GMT

Thanks. Updated.

Re: Macro code to export in excel

anirudhs — Wed, 28 Mar 2018 16:50:02 GMT

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

Re: Macro code to export in excel

Reeza — Wed, 28 Mar 2018 16:55:08 GMT

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

Re: Macro code to export in excel

anirudhs — Tue, 03 Apr 2018 10:51:10 GMT

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

Re: Macro code to export in excel

anirudhs — Tue, 03 Apr 2018 10:53:58 GMT

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

Re: Macro code to export in excel

Tom — Tue, 03 Apr 2018 13:17:17 GMT

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

Re: Macro code to export in excel

anirudhs — Tue, 03 Apr 2018 13:24:37 GMT

Yeh found it, and is running but taking too long time to execute.

Re: Macro code to export in excel

Tom — Tue, 03 Apr 2018 13:34:27 GMT

How many XLSX files are you generating?

Why are you generating so many XLSX files?

Re: Macro code to export in excel

anirudhs — Tue, 03 Apr 2018 13:35:52 GMT

Extremely sorry to update you @Tom the code you send is working but it is exporting the xlsx city but those excel contains data of all the cities,
requirement is that the excel should only contain data with the city names.
i.e. if data set contains 500 records of city pune then the excel DAC_Final_Pune.xlsx will have those 500 records from the data set only.

Re: Macro code to export in excel

Tom — Tue, 03 Apr 2018 13:50:43 GMT

So PROC EXPORT does not support the WHERE statement?
Did you try converting to using WHERE= dataset option on the input data set?

Try it for one city value to see if it works.

If you cannot get PROC EXPORT to honor the WHERE then you could add a data step to create the subset and then export that new dataset.

Re: Macro code to export in excel

anirudhs — Tue, 03 Apr 2018 14:16:47 GMT

This code for export works fine:
proc export
data=WORK.DAC_3 (where=(City='MUMBAI'))
dbms=xlsx
outfile="\\10.4.1.205\Upload\DAC_FINAL_MUMBAI.xlsx"
replace;
run;

Re: Macro code to export in excel

anirudhs — Tue, 03 Apr 2018 14:20:24 GMT

@Reeza :

Your macro code also is giving error, mentioned

NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

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.
90 + proc export data=&WORK.DAC_3 (where=(city="ZIRAKPUR")) outfile="\\10.4.1.205\Upload\DAC_FINAL_ZIRAKPUR.xlsx" dbms=xlsx
_
22
76
90 !+replace; run;;
ERROR 22-322: Expecting a name.

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

Re: Macro code to export in excel

art297 — Tue, 03 Apr 2018 14:21:31 GMT

Then just modify @Tom's code to use the where datastep option like you showed in your example. i.e.:

proc sort data=WORK.CAPFIRST_ENCOLLEC_MASTER;
  by city;
run;

data _null_;
  set WORK.CAPFIRST_ENCOLLEC_MASTER ;
  by city;
  if first.city ;
  call execute(catx(' '
   ,'proc export'
   ,'data=WORK.CAPFIRST_ENCOLLEC_MASTER (where=(city ='
   ,quote(trim(city))
   ,')) dbms=xlsx'
   ,'outfile=',quote(cats('\\10.4.1.205\Upload\DAC_FINAL_',city,'.xlsx'))
   ,'replace;'
   ,'run;'
   ));
run;

Art, CEO, AnalystFinder.com