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.
'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;
Hi,
Just add:
if _n_=1 then put "Code","Name","State";
before the put (_all_) (~);
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.
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.";
...
'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;
Works perfectly! Thanks for all the quick responses.
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
Thanks for the response Tom, but a solution was already provided by Hai.kuo.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.