BookmarkSubscribeRSS Feed
Kim_HOU
Calcite | Level 5

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

3 REPLIES 3
art297
Opal | Level 21

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

art297
Opal | Level 21

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.

Kim_HOU
Calcite | Level 5

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1092 views
  • 6 likes
  • 2 in conversation