Adding Column Names Using PUT Statement

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Adding Column Names Using PUT Statement

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.


Accepted Solutions
Solution
‎06-30-2014 10:25 AM
Respected Advisor
Posts: 3,156

Re: Adding Column Names Using PUT Statement

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,997

Re: Adding Column Names Using PUT Statement

Hi,

Just add:

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

before the put (_all_) (~);

New Contributor
Posts: 4

Re: Adding Column Names Using PUT Statement

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.

Super User
Super User
Posts: 7,997

Re: Adding Column Names Using PUT Statement

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.";

...

Solution
‎06-30-2014 10:25 AM
Respected Advisor
Posts: 3,156

Re: Adding Column Names Using PUT Statement

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

New Contributor
Posts: 4

Re: Adding Column Names Using PUT Statement

Works perfectly!  Thanks for all the quick responses.

Super User
Super User
Posts: 7,077

Re: Adding Column Names Using PUT Statement

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_) (Smiley Happy ;

run;


Code,Name,State

1,John,Pennsylvania

2,Jeff,Maryland

New Contributor
Posts: 4

Re: Adding Column Names Using PUT Statement

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 4062 views
  • 4 likes
  • 4 in conversation