Macro code to export in excel

[anirudhs]

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.

[Tom]

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

[Reeza]

---

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

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]

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]

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

[Tom]

Thanks. Updated.

[Reeza]

---

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

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]

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

[anirudhs]

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]

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

---

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

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

[Tom]

How many XLSX files are you generating?

Why are you generating so many XLSX files?