Express "PUT all variables" in a data step to export SAS data

Question

Goal: export an entire SAS dataset to a tab-delimited text file using a data step.

Problem: In every example that I can find, such as this one, one must specify every variable in the data step following the PUT statement. Isn't there a simple method to just ask for "all" of the variables?

I have already tried using PUT _ALL_, but that includes the variable names in every row of the output, instead of just outputtin the values.

1) Why not PROC EXPORT with DBMS=TAB? 2) Is order of variables important? — vasja, Apr 28 '14 at 18:25
@vasja, I happen to want also to specify the ENCODING option in my datastep, and I'm not aware of such a capability in PROC EXPORT. — zkurtz, Apr 28 '14 at 18:29
Zkurtz... include everything you are trying to do... I suspect a data step may not be able to do what you want. — Keneni, Apr 28 '14 at 18:30

Joe · Answer 1 · 2014-04-28T20:06:01.127

6

If you want to do it in a data step, AND don't want to specify things, but really don't want PROC EXPORT making decisions for you, AND have data that's reasonably easily specified (nothing fancy like dates you want in a special format), you can do this.

proc sql;
select name into :namelist separated by ' ' 
from dictionary.columns
where libname='SASHELP' and memname='CLASS'; *these are the libname/dataset name you want;
quit; 

filename blah temp;
data _null_;
set sashelp.class;
file blah dlm='09'x;
put &namelist.;
run;

You can customize it some depending on your needs by modifying the sql query (such as if you have a set of variables with _date perhaps you could modify it to add name||' :date9.' in the sql query for those.

Note that the libname/memname/name fields in dictionary.columns are typically upper case. They are not always upper case, I don't think, but they almost always are. (Some RDBMSs for example can override this, I believe).

edited Apr 28 '14 at 20:06

answered Apr 28 '14 at 18:29

Joe

62,789
6
49
67

3

working through this -- just noticed that evidently it is essential that 'SASHELP' and 'CLASS' are uppercase. – zkurtz Apr 28 '14 at 20:01
curious about the function of the 'filename blah temp' line – zkurtz Apr 28 '14 at 20:03
1

I'm just lazy. That's me writing out to a temporary file I won't have to delete later. :) `temp` as a keyword on a filename statement means it's written to your work directory. – Joe Apr 28 '14 at 20:05
Correct - the memname / name dictionary fields are not always uppercase, even for SAS datasets. Something to keep in mind.. – Allan Bowe Apr 29 '14 at 08:41

score 1 · Answer 2 · answered Apr 28 '14 at 18:38

1

@Joe's brilliant and quick as always.

For reference, many options, including ENCODING are possible with FILENAME. Those should be usable anywhere file path is allowed.

FILENAME myfile "E:\test.csv" encoding=wlatin2;

PROC EXPORT
DATA=sashelp.class
OUTFILE=myfile
DBMS=TAB REPLACE;
run;

answered Apr 28 '14 at 18:38

vasja

4,732
13
15

I tested this, and it looks like it works correctly. – Joe Apr 28 '14 at 19:41

score 0 · Answer 3 · edited Mar 20 '17 at 19:58

In a datastep I find when I just want to get a list of the values using

put (_all_)(=/);

For example:

data a; 
  ref=today();
  monday = intnx('week1.2',ref,-1,'Begin');
  sunday = intnx('week1.2',ref,-1,'End');
  week_end_Friday=intnx('day',(intnx('weeks',ref,0,'end')),-1);
  week_end_cur=intnx('weeks',ref,0,'end');
  format _all_ weekdate.; 
  put (_all_)(=/);
run;

/* or to look at a few records or modify for specific conditions */

data test;
  set test;
  if _n_ < 5 then do;
    PUT (_ALL_)(=/);  
  end;
run;

score 0 · Answer 4 · answered Mar 20 '17 at 20:10

You need to use both a variable list and format list to use the _ALL_ keyword in the PUT statement and not have it generate the names. The format list does not need to contain any actual formats but it cannot be empty. So this code will create a CSV file, without column headers.

data _null_;
  file 'want.csv' dsd ;
  set have ;
  put (_all_) (+0) ;
run;

If you do want column headers there are a couple of methods you could use. Here is one that uses PROC TRANSPOSE to generate a dataset with the variable names. This can be used to write the header row and then the above code can be used with just the addition of the MOD keyword to the FILE statement to append the data records.

proc transpose data=have(obs=0) out=names ;
  var _all_;
run;
data _null_;
  file 'want.csv' dsd ;
  set names end=eof;
  put _name_ @ ;
  if eof then put;
run;

Express "PUT all variables" in a data step to export SAS data

4 Answers4

Linked

Related