Hello, I need to reformat some data and export it into a text file with a very specific format. I'm rusty with SAS and not sure where to start, so any help would be gratefully appreciated!
The data looks like this:
Title | Type | Year | State | State2 | State3 |
Category1 | ST | 1 | 24.3 | 99.9 | 22 |
Category1 | ST | 2 | 18.2 | 105.4 | 55 |
Category1 | ST | 3 | 16.3 | 112.6 | 66 |
Category1 | ST | 4 | 10.1 | 123.4 | 77 |
Category1 | ST | 5 | 8.2 | 135.7 | 88 |
Category2 | RT | 1 | 12.15 | 49.95 | 11 |
Category2 | RT | 2 | 9.1 | 52.7 | 27.5 |
Category2 | RT | 3 | 8.15 | 56.3 | 33 |
Category2 | RT | 4 | 5.05 | 61.7 | 38.5 |
Category2 | RT | 5 | 4.1 | 67.85 | 44 |
And I need to create a text file that looks like this:
*Title Category1 Type ST
Year State State2 State3
1 24.3 99.9 22
2 18.2 105.4 55
3 16.3 112.6 66
4 10.1 123.4 77
5 8.2 135.7 88
*Title Category2 Type RT
Year State State2 State3
1 12.15 49.95 11
2 9.1 52.7 27.5
3 8.15 56.3 33
4 5.05 61.7 38.5
5 4.1 67.85 44
The tricky part is embedding the heading information each time the category changes.
Thanks in advance for your help!
/* Create the input data */
data have;
infile datalines dsd dlm='|';
input Title:$15. Type:$2. Year State State2 State3;
datalines;
Category1|ST|1|24.3|99.9|22
Category1|ST|2|18.2|105.4|55
Category1|ST|3|16.3|112.6|66
Category1|ST|4|10.1|123.4|77
Category1|ST|5|8.2|135.7|88
Category2|RT|1|12.15|49.95|11
Category2|RT|2|9.1|52.7|27.5
Category2|RT|3|8.15|56.3|33
Category2|RT|4|5.05|61.7|38.5
Category2|RT|5|4.1|67.85|44
;
/* Do the work */
/* Sort the data for BY group processing */
proc sort data=have;
by Title Type;
run;
data _null_;
/* Read data with BY statement. */
set have;
by Title Type;
file "c:/temp/want.csv";
/* Use FIRST. to detect new group, write header */
if first.type then do;
put "Title " Title "Type " type;
end;
/* Write values */
put Year State State2 State3;
run;
Result:
Title Category1 Type ST 1 24.3 99.9 22 2 18.2 105.4 55 3 16.3 112.6 66 4 10.1 123.4 77 5 8.2 135.7 88 Title Category2 Type RT 1 12.15 49.95 11 2 9.1 52.7 27.5 3 8.15 56.3 33 4 5.05 61.7 38.5 5 4.1 67.85 44
/* Create the input data */
data have;
infile datalines dsd dlm='|';
input Title:$15. Type:$2. Year State State2 State3;
datalines;
Category1|ST|1|24.3|99.9|22
Category1|ST|2|18.2|105.4|55
Category1|ST|3|16.3|112.6|66
Category1|ST|4|10.1|123.4|77
Category1|ST|5|8.2|135.7|88
Category2|RT|1|12.15|49.95|11
Category2|RT|2|9.1|52.7|27.5
Category2|RT|3|8.15|56.3|33
Category2|RT|4|5.05|61.7|38.5
Category2|RT|5|4.1|67.85|44
;
/* Do the work */
/* Sort the data for BY group processing */
proc sort data=have;
by Title Type;
run;
data _null_;
/* Read data with BY statement. */
set have;
by Title Type;
file "c:/temp/want.csv";
/* Use FIRST. to detect new group, write header */
if first.type then do;
put "Title " Title "Type " type;
end;
/* Write values */
put Year State State2 State3;
run;
Result:
Title Category1 Type ST 1 24.3 99.9 22 2 18.2 105.4 55 3 16.3 112.6 66 4 10.1 123.4 77 5 8.2 135.7 88 Title Category2 Type RT 1 12.15 49.95 11 2 9.1 52.7 27.5 3 8.15 56.3 33 4 5.05 61.7 38.5 5 4.1 67.85 44
Thank you so much! This did exactly what I needed.
It is a good idea when asking about creation of a custom text file to provide the actual specifications required in the file.
I suspect that you may have left out some requirements like column alignment and/or column number positions or if the output supposed to be TAB delimited and not spaces. Pagination may also be an issue. These are easy to add but may need to specify if the results shown by @SASJedi don't "work" with what other application may want to read the file.
Good points thanks! I needed a simple space delineated file but you're correct, next time I'll keep that in mind.
/*
If you don't have a big table,
you could try ODS CSCALL .
*/
data have;
infile datalines dsd dlm='|';
input Title:$15. Type:$2. Year State State2 State3;
datalines;
Category1|ST|1|24.3|99.9|22
Category1|ST|2|18.2|105.4|55
Category1|ST|3|16.3|112.6|66
Category1|ST|4|10.1|123.4|77
Category1|ST|5|8.2|135.7|88
Category2|RT|1|12.15|49.95|11
Category2|RT|2|9.1|52.7|27.5
Category2|RT|3|8.15|56.3|33
Category2|RT|4|5.05|61.7|38.5
Category2|RT|5|4.1|67.85|44
;
ods noresults;
ods _all_ close;
ods csvall file="c:\temp\want.csv";
options nobyline;
title "*Title: #byval1 Type: #byval2";
proc report data=have nowd ;
by title type;
column Year State State2 State3;
define Year/display;
run;
ods csvall close;
Thank you! The only question I have is whether it's possible to get rid of the quotations around the field names? eg "Year","State","State2","State3".
@YEGUser wrote:
Thank you! The only question I have is whether it's possible to get rid of the quotations around the field names? eg "Year","State","State2","State3".
You can but if those column headers could ever possibly contain the delimiter character(s) used by the target program shouldn't. The quotes for most nice processes protect the using program from treating embedded delimiters from being used as delimiter. If your file is space delimited then "New York" gets treated as one item, New York as two.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.