Help using Base SAS procedures

array inside a proc export (inside a call execute)

Reply
New Contributor
Posts: 2

array inside a proc export (inside a call execute)

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

PROC Star
Posts: 7,492

array inside a proc export (inside a call execute)

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

PROC Star
Posts: 7,492

array inside a proc export (inside a call execute)

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.

New Contributor
Posts: 2

array inside a proc export (inside a call execute)

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

Ask a Question
Discussion stats
  • 3 replies
  • 357 views
  • 6 likes
  • 2 in conversation