Conditionally Exporting SAS to Excel

[LearningSAS2]

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

[CurtisMackWSIPP]

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.

[LearningSAS2]

Hello, I think it looks like I am creating datasets because I shortened my file paths for outfile and datafile (it is very long and took up a lot of space).

Maybe I am misunderstanding what you mean? Sorry and thank you!

[CurtisMackWSIPP]

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;

[LearningSAS2]

Thank You!
I have no idea SAS could do this.

Is there any way ODS can make a separate .xlsx for each state? Right now I have 1 excel file with many tabs.

Thank You!
Joseph

[CurtisMackWSIPP]

Unfortunately, I do not know of a way of doing that without your approach or macros.

[Shmuel]

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.

[LearningSAS2]

Thank You

I attempted the Call Execute (I have not learned this yet), but I am seeing this error:
ERROR: ""\\Test\PASEP20.xlsx"" is not a valid name.

I will most likely to with either ODS or your Proc Freq method to check for states, but just wondering what the above Error means and what I am doing wrong.

Thank You both!

[CurtisMackWSIPP]

Its a typo:

outfile="c:\Test\AK &today..xlsx"

[CurtisMackWSIPP]

That must be a valid path in your system.

[LearningSAS2]

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!

[Shmuel]

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;

[LearningSAS2]

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\

[Shmuel]

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.