A reader posed a question in the comments to an earlier Jedi SAS Trick, asking how to write the results of a DS2 DATA _NULL_ program to a text file. It's an interesting question, as DS2 currently has no text file handling statements or capabilities. Take, for example, this traditional SAS DATA step which writes output to a CSV file:
filename out 'test.csv'; data _null_; set class; file out dsd; put Name Sex Age Height Weight; run; |
The output file, test.csv, looks like this:
Alfred,M,14,69,112.5 Alice,F,13,56.5,84 Barbara,F,13,65.3,98 Carol,F,14,62.8,102.5 Henry,M,14,63.5,102.5
Without the benefit of a FILE statement, how could I reproduce these results using DS2? Well, the first step is to convert the program to DS2:
proc ds2; data _null_; method run(); set class; put Name Sex Age Height Weight; end; enddata; run; quit; |
This program produces this output in the SAS log:
Alfred M 14 69 112.5 Alice F 13 56.5 84 Barbara F 13 65.3 98 Carol F 14 62.8 102.5 Henry M 14 63.5 102.5
Not quite what I was looking for. However, I know that if I remove the _NULL_ keyword from the DATA statement, the DS2 data program sends its result set to the Output Delivery System (ODS) as a report, much like an SQL query would. Because I don't want a title in my output, I'll first execute a TITLE; statement:
title; proc ds2; data; method run(); set class; end; enddata; run; quit; |
The result looks like this:
Now, the default ODS destination is HTML, but I want a text file. As a matter of fact, I want a CSV file - and there is a nice ODS destination just made for that. To completely duplicate the CSV file produced by the traditional SAS DATA step program, I'll need to suppress the column names in the first row of ODS output. This code should do the trick:
title; ods tagsets.csv file=out options(table_headers="no"); proc ds2; data; method run(); set class; end; enddata; run; quit; ods tagsets.csv close; |
And the output file, test.csv, looks like this:
"Alfred","M",14,69,112.5 "Alice","F",13,56.5,84 "Barbara","F",13,65.3,98 "Carol","F",14,62.8,102.5 "Henry","M",14,63.5,102.5
The automatic addition of the quotes around the text variable values is a nice touch, making the output file compliant with the ITEF CSV specification. So, for now I'll declare victory :-)
Until the next episode, may the SAS be with you!
Mark
P.S. Interested in a deeper dive into DS2? Why not check out my new book "Mastering the SAS® DS2 Procedure":
2 Comments
I'm trying to replicate the old school file output just using put statements, but I am having trouble getting that working in DS2. For example, I need to create some custom text output between my variable output; so, CSV options won't work. Can you discuss how to do that in DS2?
DS2 doesn't process unstructured data, so you can't write to a text file using PUT statements in DS2 (which is why I had to come up with this ODS trick). It would be helpful if you post a sample of the data that you have (see the DATA2DATASTEP macro blog for help with that) and a mock up of the output you want. If you can do that, I'd be happy to take a peek!
Stay SASy, Matt:-)
Mark