Proc Transpose for a file with only 2 variables

[Wolverine]

I've assembled some frequency tables into a single file that I am now trying to organize into a table. I'm trying to use Proc Transpose, but all the examples I'm finding have at least 3 variables. My file only has 2 variables:

Group	COUNT_ROWPERCENT
American_Ind_pt	76(0.50%)
Asian_pt	635(4.15%)
Black_pt	2176(14.21%)
Hispanic_pt	1098(7.17%)
White_pt	9702(63.36%)
missing_pt	1486(9.70%)
multi_race_pt	139(0.91%)

Here is my code:

PROC TRANSPOSE data=HAVE out=WANT;
	BY group;
	ID group;
	VAR COUNT_ROWPERCENT; RUN;

Which produces this:

Group	_NAME_	American_Ind_pt	Asian_pt	Black_pt	Hispanic_pt	White_pt	missing_pt	multi_race_pt
American_Ind_pt	COUNT_ROWPERCENT	76(0.50%)
Asian_pt	COUNT_ROWPERCENT		635(4.15%)
Black_pt	COUNT_ROWPERCENT			2176(14.21%)
Hispanic_pt	COUNT_ROWPERCENT				1098(7.17%)
White_pt	COUNT_ROWPERCENT					9702(63.36%)
missing_pt	COUNT_ROWPERCENT						1486(9.70%)
multi_race_pt	COUNT_ROWPERCENT							139(0.91%)

When I actually want this:

American_Ind_pt	Asian_pt	Black_pt	Hispanic_pt	White_pt	missing_pt	multi_race_pt
76(0.50%)	635(4.15%)	2176(14.21%)	1098(7.17%)	9702(63.36%)	1486(9.70%)	139(0.91%)

[PaigeMiller]

You are telling SAS you want PROC TRANSPOSE to work BY GROUP; but your words and desired output say you do NOT want PROC TRANSPOSE to work BY GROUP.

It looks like you are trying to create a report. If so, you really really really really ought to do this in PROC REPORT; and you really really really really should not be using PROC TRANSPOSE for this. (and by using PROC REPORT you won't have to have silly column names that are computer variable names like American_Ind_pt; you can have real words as column names, like American Indian Patients or something like that)

[Wolverine]

---

@PaigeMiller wrote:

It looks like you are trying to create a report. If so, you really really really really ought to do this in PROC REPORT; and you really really really really should not be using PROC TRANSPOSE for this. (and by using PROC REPORT you won't have to have silly column names that are computer variable names like American_Ind_pt; you can have real words as column names, like American Indian Patients or something like that)

---

A fair point, but this information is getting combined with other output to create a single large table. I just needed to get this file into the correct layout so it can be combined. Once that's done, I can use other syntax I have that will correct the column names, add titles, etc.

[Reeza · Accepted solution]

Remove the BY statement.

Worked example:

data class;
set sashelp.class;
keep name age;
run;

proc transpose data=class out=want;
id name;
var age;
run;

---

@Wolverine wrote:

I've assembled some frequency tables into a single file that I am now trying to organize into a table. I'm trying to use Proc Transpose, but all the examples I'm finding have at least 3 variables. My file only has 2 variables:

Group	COUNT_ROWPERCENT
American_Ind_pt	76(0.50%)
Asian_pt	635(4.15%)
Black_pt	2176(14.21%)
Hispanic_pt	1098(7.17%)
White_pt	9702(63.36%)
missing_pt	1486(9.70%)
multi_race_pt	139(0.91%)

Here is my code:

PROC TRANSPOSE data=HAVE out=WANT;
	BY group;
	ID group;
	VAR COUNT_ROWPERCENT; RUN;

Which produces this:

Group	_NAME_	American_Ind_pt	Asian_pt	Black_pt	Hispanic_pt	White_pt	missing_pt	multi_race_pt
American_Ind_pt	COUNT_ROWPERCENT	76(0.50%)
Asian_pt	COUNT_ROWPERCENT		635(4.15%)
Black_pt	COUNT_ROWPERCENT			2176(14.21%)
Hispanic_pt	COUNT_ROWPERCENT				1098(7.17%)
White_pt	COUNT_ROWPERCENT					9702(63.36%)
missing_pt	COUNT_ROWPERCENT						1486(9.70%)
multi_race_pt	COUNT_ROWPERCENT							139(0.91%)

When I actually want this:

American_Ind_pt	Asian_pt	Black_pt	Hispanic_pt	White_pt	missing_pt	multi_race_pt
76(0.50%)	635(4.15%)	2176(14.21%)	1098(7.17%)	9702(63.36%)	1486(9.70%)	139(0.91%)

---