Help Creating Custom Text File

[YEGUser]

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! 

[SASJedi · Accepted solution]

/* 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

[YEGUser]

Thank you so much! This did exactly what I needed.

[ballardw]

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.

[YEGUser]

Good points thanks!  I needed a simple space delineated file but you're correct, next time I'll keep that in mind.

[Ksharp]

/*
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;

[YEGUser]

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

[ballardw]

---

@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.