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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: