Macro code to export in excel

[anirudhs]

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.

[Tom]

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.

[anirudhs]

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;

[art297]

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

[anirudhs]

Nope its not working, and the macro code which is posted by @Reeza only creats seperate sheet with city names and variable name but no data corresponding to the city.

[Reeza]

---

@anirudhs wrote:
Nope its not working, and the macro code which is posted by @Reeza only creats seperate sheet with city names and variable name but no data corresponding to the city.

---

Post your non working code. 

If my macro is not working, you likely forgot to change something within the code to match your data.

[Tom]

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;

So use the CALL EXECUTE to generate code in that format.

[anirudhs]

like exactly how??

[art297]

The way I showed you in my post a few minutes ago.

Art, CEO, AnalystFinder.com

[Tom]

Art posted the code, but you should be able to do it yourself.
The complete code that Art posted uses just two procs (SORT and EXPORT) and a single data step.
The data step uses just the functions TRIM(), QUOTE(), CATX(), CATS() and CALL EXECUTE().
You also need to understand the BY statement and FIRST./LAST. flags.
You also need to understand the WHERE= dataset option.

[anirudhs]

yes i tried it .... but the excel with city names excel is exporting the whole dataset not the data pertaining to the city only.

[art297]

Post your log. The following example using sashelp.class appears to work quite well:

proc sort data=sashelp.class out=class;
  by sex;
run;

data _null_;
  set WORK.class ;
  by sex;
  if first.sex ;
  call execute(catx(' '
   ,'proc export'
   ,'data=WORK.class (where=(sex ='
   ,quote(trim(sex))
   ,')) dbms=xlsx'
   ,'outfile=',quote(cats('/folders/myfolders/DAC_FINAL_',sex,'.xlsx'))
   ,'replace;'
   ,'run;'
   ));
run;

Art, CEO, AnalystFinder.com

[Reeza · Accepted solution]

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;

[anirudhs]

Thanks A lot @Reeza its working as expected ... but its taking long time to export .
So how can we reduce the time of the execution of the code.

[Reeza]

Assuming your data sets are not large, you're likely running into a network speed issue more than anything. 

There's no real way to speed up the code, except perhaps add an index to the data set ahead of time for the City so the WHERE executes faster, but I'd strongly suspect that the bottleneck in your process is writing the files not the filtering.