BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pjm294
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

'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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Just add:

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

before the put (_all_) (~);

pjm294
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

...

Haikuo
Onyx | Level 15

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

pjm294
Calcite | Level 5

Works perfectly!  Thanks for all the quick responses.

Tom
Super User Tom
Super User

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

pjm294
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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