topic Proc Transpose for a file with only 2 variables in SAS Programming

Proc Transpose for a file with only 2 variables

Wolverine — Thu, 26 Oct 2023 19:21:43 GMT

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%)

Re: Proc Transpose for a file with only 2 variables

PaigeMiller — Thu, 26 Oct 2023 19:44:14 GMT

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)

Re: Proc Transpose for a file with only 2 variables

Reeza — Thu, 26 Oct 2023 19:53:57 GMT

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%)

Re: Proc Transpose for a file with only 2 variables

Wolverine — Thu, 26 Oct 2023 20:17:51 GMT

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