topic Re: Adding Column Names Using PUT Statement in SAS Programming

Adding Column Names Using PUT Statement

pjm294 — Mon, 30 Jun 2014 13:50:46 GMT

I am using the following code to produce a text file:

filename outfile 'C:\Users\pjm294\Desktop\somefile.txt';

DATA test;

file outfile dsd dlm=',';

set work.somedata;

put (_all_) (~);

RUN;

The output I desire is as follows:

"Code","Name","State"

"1","John","Pennsylvania"

"2","Jeff","Maryland"

However the above code lacks the column names I need. The code produces the following output:

"1","John","Pennsylvania"

"2","Jeff","Maryland"

Is there a way to add the column names using the PUT statement? The file I am creating it part of a process, which this file goes to a vendor for further processing. Their programming requires the file to have column names and all data elements in double quotes with each field separated by commas.

Re: Adding Column Names Using PUT Statement

RW9 — Mon, 30 Jun 2014 14:01:09 GMT

Hi,

Just add:

if _n_=1 then put "Code","Name","State";

before the put (_all_) (~);

Re: Adding Column Names Using PUT Statement

pjm294 — Mon, 30 Jun 2014 14:04:49 GMT

That answer works, however I should have specified in the original post that this was just an example. The actual process has hundreds of field names that change from file to file depending on the data. I want to automate the entire process and if I have to add the individual column names as suggested by your answer I am no further ahead. Apologies for not clarifying in the original question.

Re: Adding Column Names Using PUT Statement

RW9 — Mon, 30 Jun 2014 14:19:14 GMT

No probs.

proc sql;

select name

into :VARL_LIST separated be '","'

from SASHELP.VCOLUMN

where LIBNAME="xxxx" and MEMNAME="xxxx";

run;

data test;

...

if _n_=1 then put "&VAR_LIST.";

...

Re: Adding Column Names Using PUT Statement

Haikuo — Mon, 30 Jun 2014 14:25:15 GMT

's suggestion is on the right direction, except some details need to be tweaked. Since you used DSD, meaning you want your output data to be comma delimited and quoted, you would need more work to polish the macro variable:

filename outfile 'C:\Users\pjm294\Desktop\somefile.txt';

proc sql;

select quote(CATS(name)) into :name SEPARATED BY ',' from dictionary.columns where LIBNAME='WORK' AND MEMNAME='SOMEDATA';QUIT;

DATA test;

file outfile dsd dlm=',';

set SOMEDATA;

IF _N_=1 THEN PUT "%BQUOTE(&NAME)";

put (_all_) (~);

RUN;

Re: Adding Column Names Using PUT Statement

pjm294 — Mon, 30 Jun 2014 14:34:36 GMT

Works perfectly! Thanks for all the quick responses.

Re: Adding Column Names Using PUT Statement

Tom — Tue, 01 Jul 2014 00:07:09 GMT

Why do you want quotes around EVERY value? SAS will put in the quotes where they are needed. They take up a lot of extra space in the file and unless you are forced to send this data to some brain dead system they should not be needed.

You can also write the names in a separate step before the data. For example here is method using PROC TRANSPOSE to generate the variable names into a dataset for you.

proc transpose data=work.somedata(obs=0) out=names ;

var _all_;

run;

data _null_;

set names end=eof ;

file outfile dsd lrecl=30000 ;

put _name_ @ ;

if eof then put ;

run;

Then in your data step that writes the data add the MOD option to the FILE statement to append that data to the file with the header line.

data _null_;

set work.somedata ;

file outfile dsd MOD lrecl=30000 ;

put (_all_) (:) ;

run;

Code,Name,State

1,John,Pennsylvania

2,Jeff,Maryland

Re: Adding Column Names Using PUT Statement

pjm294 — Tue, 01 Jul 2014 12:21:43 GMT

Thanks for the response Tom, but a solution was already provided by Hai.kuo.