topic array inside a proc export (inside a call execute) in SAS Procedures

array inside a proc export (inside a call execute)

Kim_HOU — Tue, 06 Sep 2011 15:15:49 GMT

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

array inside a proc export (inside a call execute)

art297 — Tue, 06 Sep 2011 16:35:43 GMT

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

array inside a proc export (inside a call execute)

art297 — Wed, 07 Sep 2011 04:11:29 GMT

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.

array inside a proc export (inside a call execute)

Kim_HOU — Wed, 07 Sep 2011 14:26:03 GMT

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