Express “PUT all variables” in a data step to expo

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.

Answer 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;

Answer 2

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).

Answer 3

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;

Answer 4

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;