Hello,
Warning: I am a SAS Novice so advanced help will not help me, if that makes sense. I have spent hours googling trying to figure this out and would like to ask for help!
Goal:
I am trying to parse out a spreadsheet with data from many US states into separate spreadsheets, with a separate spreadsheet for each state.
My data source spreadsheet has variables are Location, Cost, etc, etc. State. State is what I am dividing out by.
I am not yet advanced enough to learn how to do macros/SQL, so I am okay with having 50 export statements (1 for each state).
My goal here is to make it so SAS does not export/create a spreadsheet if a state does not appear in Testdata.xlsx.
Right now my code creates a spreadsheet for states even if they do not appear under the variable name/column header 'State' in Testdata.xlsx.
So if my spreadsheet has 10 rows/obs of data, each one for a different US state, I end up creating 50 total exports, of which 40 are useless.
This is what I have so far:
PROC IMPORT OUT = Work.Separate
datafile="\\Test\Testdata.xlsx"
dbms=xlsx replace;
sheet="Sheet1";
getnames=yes;
run;
%let today=&sysdate;
data AL;
set Work.Separate;
if State='AL';
proc export data=AL
outfile="\\Test\AL &today..xlsx"
dbms=xlsx replace;
run;
data AZ;
set Work.Separate;
if State='AK';
proc export data=AK
outfile="\\Test\AK &today..xlsx"
dbms=xlsx replace;
run;
Thank You!!
Joseph
Could we get some clarification? You say you are creating spreadsheets, but this is creating SAS datasets. If you are generating spreadsheets using ODS, most output procedures (proc report, print, others) have a "BY" statement that will create separate tables by value for you.
I'm saying that you can handle this in the output step with a "BY" variable. Here is an example:
proc sort data= sashelp.baseball out= sorted; by team; run; ods excel file="c:\temp\temp.xlsx"; proc print data=sorted; by team; run; ods excel close;
1) In order to eliminate the misunderstanding you should use the running man icon to post the code
and better separate the data step from a proc as shown here:
data AL;
set Work.Separate;
if State='AL';
run;
proc export data=AL
outfile="\\Test\AL &today..xlsx"
dbms=xlsx replace;
run;
2) In your 2nd state you probably have a typo:
data AK; /* not AZ; */
set Work.Separate;
if State='AK';
run;
proc export data=AK
outfile="\\Test\AK &today..xlsx"
dbms=xlsx replace;
run;
I can think of few methods doing what you want - exporting only those states given in the input:
a) Run PROC FREQ to check what states are in the input and write PROC EXPORT only for them
proc freq data=Work.Separate;
table state;
run;
/* check results then add ... */
data AL AK ;
set Work.Separate;
if state = 'AL' then output AL; else
if state = 'AK' then output AK; /* ... else more states */
run;
proc export data=AL
outfile="\\Test\AL &today..xlsx"
dbms=xlsx replace;
run;
proc export data=AK
outfile="\\Test\AK &today..xlsx"
dbms=xlsx replace;
run;
b) Use CALL EXECUTE from a datstep without the need to cteare a dataset per each state, as in:
%let today = SEP20;
data _NULL_;
set work.separated;
by state notsorted;
if first.state then
call execute ("proc export data" || strip(state) ||
cats('outfile="\\Test\',strip(state),"&today..xlsx",'" dbms=xlsx replace; run;" '));
run;
c) Using a macro, but that seems to be more complicated for you now.
Its a typo:
outfile="c:\Test\AK &today..xlsx"
Sorry, still encountering the not valid name error:
I am looking at my log error:
NOTE: Line generated by the CALL EXECUTE routine.
1 + proc export dataPAoutfile="C:\Users\JPark\Documents\Test\AK SEP20xlsxPAAK SEP20.xlsx"
-------------
1
1 !+dbms=xlsx replace; run;""
ERROR: ""C:\Users\JPark\Documents\Test\AK SEP20xlsxPAAK SEP20.xlsx"" is not a valid name.
NOTE: PROCEDURE EXPORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: The SAS System stopped processing this step because of errors.
My code is:
PROC IMPORT OUT = Work.Cases
datafile="C:\Users\JPark\Documents\Test\Testdata.xlsx"
dbms=xlsx replace;
sheet="Sheet1";
getnames=yes;
run;
%let today = SEP20;data _NULL_;
set Work.Cases;
by state notsorted;
if first.state then
call execute ("proc export data" || strip(state) ||
cats('outfile="C:\Users\JPark\Documents\Test\',strip(state),"AK &today..xlsx",'" dbms=xlsx replace; run;" '));
run;
Thank you for your help!
I fixed the code but could not test it:
%let today = SEP20;
data _NULL_;
set Work.Cases;
by state notsorted;
if first.state then
call execute ("proc export data=" || strip(state) || " " ||
cats('outfile="C:\Users\JPark\Documents\Test\',strip(state),"_&today..xlsx",'" dbms=xlsx replace; run;'));
run;
dataPAoutfile has green dashed lines under it, before it stays there is an error (is not a valid name)
proc export dataPAoutfile="\\JPark\Documents\Test\
ERROR: ""\\Test\PASEP20.xlsx"" is not a valid name.
it is probably a state PA concatenated to the date 'SEP20' as assigned to the macro variable TODAY.
There are double - double quotes - generated. I have edited the posted code after testing it. Please try the last edited code.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.