Databases
StackOverflow2013
Postsdbo
POSAS: Limiting variables in PROC EXPORT
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSAS: Limiting variables in PROC EXPORT
    primarykey
    data
    text
    <p>I have a PROC EXPORT question that I am wondering if you can answer. </p> <p>I have a SAS dataset with 800+ variables and over 200K observations and I am trying to export a subset of the variables to a CSV file (i.e. I need all records; I just don’t want all 800+ variables). I can always create a temporary dataset “KEEP”ing just the fields I need and run the EXPORT on that temp dataset, but I am trying to avoid the additional step because I have a large number of records. </p> <p>To demonstrate this, consider a dataset that has three variables named x, y and z. But, I want the text file generated through PROC EXPORT to only contain x and y. My attempt at a solution below does not quite work. </p> <p><strong>The SAS Code</strong></p> <p>When I run the following code, I don’t get exactly what I need. If you run this code and look at the text file that was generated, it has a comma at the end of every line and the header includes all variables in the dataset anyway. Also, I get some messages in the log that I shouldnt be getting. </p> <pre><code>data ds1; do x = 1 to 100; y = x * x; z = x * x * x; output; end; run; proc export data=ds1(keep=x y) file='c:\test.csv' dbms=csv replace; quit; </code></pre> <p>Here are the first few lines of the text file that was generated ("C:\test.csv")</p> <pre><code>x,y,z 1,1, 2,4, 3,9, 4,16, </code></pre> <p><strong>The SAS Log</strong></p> <pre><code>9343 proc export data=ds1(keep=x y) 9344 file='c:\test.csv' 9345 dbms=csv 9346 replace; 9347 quit; 9348 /********************************************************************** 9349 * PRODUCT: SAS 9350 * VERSION: 9.2 9351 * CREATOR: External File Interface 9352 * DATE: 30JUL12 9353 * DESC: Generated SAS Datastep Code 9354 * TEMPLATE SOURCE: (None Specified.) 9355 ***********************************************************************/ 9356 data _null_; 9357 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 9358 %let _EFIREC_ = 0; /* clear export record count macro variable */ 9359 file 'c:\test.csv' delimiter=',' DSD DROPOVER lrecl=32767; 9360 if _n_ = 1 then /* write column names or labels */ 9361 do; 9362 put 9363 "x" 9364 ',' 9365 "y" 9366 ',' 9367 "z" 9368 ; 9369 end; 9370 set DS1(keep=x y) end=EFIEOD; 9371 format x best12. ; 9372 format y best12. ; 9373 format z best12. ; 9374 do; 9375 EFIOUT + 1; 9376 put x @; 9377 put y @; 9378 put z ; 9379 ; 9380 end; 9381 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ 9382 if EFIEOD then call symputx('_EFIREC_',EFIOUT); 9383 run; NOTE: Variable z is uninitialized. NOTE: The file 'c:\test.csv' is: Filename=c:\test.csv, RECFM=V,LRECL=32767,File Size (bytes)=0, Last Modified=30Jul2012:12:05:02, Create Time=30Jul2012:12:05:02 NOTE: 101 records were written to the file 'c:\test.csv'. The minimum record length was 4. The maximum record length was 10. NOTE: There were 100 observations read from the data set WORK.DS1. NOTE: DATA statement used (Total process time): real time 0.04 seconds cpu time 0.01 seconds 100 records created in c:\test.csv from DS1. NOTE: "c:\test.csv" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.12 seconds cpu time 0.06 seconds </code></pre> <p>Any ideas how I can solve this problem? I am running SAS 9.2 on windows 7. </p> <p>Any help would be appreciated. Thanks. </p> <ul> <li>Karthik</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USKarthik
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSAS: Limiting variables in PROC EXPORT
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI realize I can use "data _null_; set ds1; file 'c:\test.csv' dlm=','; put x y; run;" to get this to work. PROC Export seems attractive because I may need to do export to an excel workbook tomorrow instead of a CSV file and the "data step" option will not work then.
      singulars
      1. POSAS: Limiting variables in PROC EXPORT
      1. USKarthik
    2. COI don't know if this works, so I'm posting it as a comment. Can you just use a data view to create the subset that you want, and then export that? I mean, can you export a view? I'm not sure...
      singulars
      1. POSAS: Limiting variables in PROC EXPORT
      1. USitzy
    3. COThat worked like a charm! I will post that as the solution. Thanks.
      singulars
      1. POSAS: Limiting variables in PROC EXPORT
      1. USKarthik
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload