I am just coming back to SAS after a few years away. My first project is somewhat complex to write, it seems. Any tips will be appreciated.
I want to take a complete data set (key) and save it as many small data sets (depid#) based on one variable's (depid) value. I think the only way to do this is like this (yet exactly this doesn't work - I think it's getting stuck with the array in the proc export file name).
data temp; set key;
array id[2] (2 4);
do i=1 to 2;
if depid=id;
call execute ('proc export data=temp
outfile="C:\My Dept\Research Studies\depid(id"
DBMS=CSV REPLACE;PUTNAMES=YES');
end;run;quit;
If there are recommendations for different steps (do I need a proc export to get .csv/.xls output files?, is there a procedure already written that does exactly what I need?) please let me know.
Regards,
Kim
The following article shows one way of doing it using a combination of proc sql and a SAS macro. It could easily be generalized to accomplish the task you want to do:
http://www.sascommunity.org/wiki/Automatically_Separating_Data_into_Excel_Sheets
If you only have X distinct groups that you want to run, and you're not trying to write production code or code that can easily be generalized for other uses, here is a rather brute force, but simple approach:
1. run your proc export but for only one record i.e. (obs=1)
proc export data=sashelp.class (obs=1)
outfile="C:\art\junk"
DBMS=CSV REPLACE;PUTNAMES=YES;
run;
2. press F4. That will get the system to recall the code
it actually submitted for your proc export
/**********************************************************************
* PRODUCT: SAS
* VERSION: 9.2
* CREATOR: External File Interface
* DATE: 05SEP11
* DESC: Generated SAS Datastep Code
* TEMPLATE SOURCE: (None Specified.)
***********************************************************************/
data _null_;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
%let _EFIREC_ = 0; /* clear export record count macro variable */
file 'C:\ART\JUNK' delimiter=',' DSD DROPOVER lrecl=32767;
if _n_ = 1 then /* write column names or labels */
do;
put
"Name"
','
"Sex"
','
"Age"
','
"Height"
','
"Weight"
;
end;
set SASHELP.CLASS(OBS=1) end=EFIEOD;
format Name $8. ;
format Sex $1. ;
format Age best12. ;
format Height best12. ;
format Weight best12. ;
do;
EFIOUT + 1;
put Name $ @;
put Sex $ @;
put Age @;
put Height @;
put Weight ;
;
end;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
if EFIEOD then call symputx('_EFIREC_',EFIOUT);
run;
3. Extract the lines you need for your code. E.g.,
data _null_;
if _n_ = 1 then do;
file "c:\art\F.csv";
put "Name" ',' "Sex" ',' "Age" ',' "Height" ',' "Weight";
file "c:\art\M.csv";
put "Name" ',' "Sex" ',' "Age" ',' "Height" ',' "Weight";
end;
set sashelp.class;
format Name $8. ;
format Sex $1. ;
format Age best12. ;
format Height best12. ;
format Weight best12. ;
if sex eq 'F' then do;
file "c:\art\F.csv";
put Name $ @;
put Sex $ @;
put Age @;
put Height @;
put Weight ;
;
end;
else if sex eq 'M' then do;
file "c:\art\M.csv";
put Name $ @;
put Sex $ @;
put Age @;
put Height @;
put Weight ;
;
end;
run;
4. Run the datastep just created in step 3 and you end up with your 2 CSV files.
Thank you, Art! I'm sorry for the delayed response. I got blocked from responding on here yesterday from a time out(?).
I enjoyed reading through the information you sent and trying to apply it. I also got some advice from a SAS friend about call execute with an array. I never could get any of the 'fancy' stuff to work.But, I went with the cumbersome brute force method you desscribed above (proc export) - using Word and the highlighting columns feature to make it reasonable to do.
It worked!
Thanks again,
Kim
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.