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.
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 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.
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.
The way I showed you in my post a few minutes ago.
Art, CEO, AnalystFinder.com
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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.