topic Simple rearrangment with Proc Transpose in SAS Programming

Simple rearrangment with Proc Transpose

Wolverine — Wed, 01 May 2024 16:31:39 GMT

I'm trying to format a frequencies output dataset. The data looks like this:

varname	COUNT	PERCENT
	20	.200000000
0	50	.500000000
1	30	.300000000

I want it to look like this:

_NAME_	0	PERCENT_0	1	PERCENT_1	missing	PERCENT_m
varname	50	.50	30	.30	20	.20

Current code:

PROC TRANSPOSE data=have out=want;
	VAR varname;
	BY COUNT PERCENT; 
RUN;

Re: Simple rearrangment with Proc Transpose

PaigeMiller — Wed, 01 May 2024 16:36:05 GMT

Do you want this wide arrangement of the data to produce a report? If so, leave the data un-transposed and use PROC REPORT which will give you a wide report on the un-transposed data.

Re: Simple rearrangment with Proc Transpose

Wolverine — Wed, 01 May 2024 16:49:57 GMT

Yes, I'm trying to create a table with about 10 variables. My plan was to transpose the Proc Freq output from each variable and then combine them into a single dataset.

I've never used Proc Report to do something like this and I'm not finding any similar examples online, so I could use some guidance.

Re: Simple rearrangment with Proc Transpose

Reeza — Wed, 01 May 2024 17:10:41 GMT

Are all the variables using 0/1/missing?

Re: Simple rearrangment with Proc Transpose

Wolverine — Wed, 01 May 2024 17:12:22 GMT

@Reeza wrote:
Are all the variables using 0/1/missing?

Yes

Re: Simple rearrangment with Proc Transpose

PaigeMiller — Wed, 01 May 2024 17:17:44 GMT

Example using SASHELP.CLASS

proc freq data=sashelp.class;
    tables sex/noprint missing out=percents;
run;        
proc report data=percents;
    columns sex,(count percent);
    define sex/across missing;
run;

The missing is not needed for variable SEX, but your example does have missings, that's why I put it in there.

Important concept: transpose to a wide data set is rarely useful, it makes your programming work HARDER. Why? Because almost every SAS data handling PROC expects long data sets, and doesn't work well (if at all) on wide data sets.

Re: Simple rearrangment with Proc Transpose

Reeza — Wed, 01 May 2024 17:49:25 GMT

More cumbersome than needed I'm sure here.

data have;
    do j=1 to 100;
        array value(10);

        do i=1 to 10;
            value(i)=rand('bernoulli', 0.4);
            _missing=rand('bernoulli', 0.05);

            if _missing=1 then
                value(i)=.;
        end;
        output;
    end;
    keep j value1-value10;
run;

ods output summary=part1;

proc means data=have stackods N NMISS SUM;
    var value1-value10;
run;

data want (keep=variable N_0 percent_0 N_1 percent_1 NMISS percent_missing);
    length variable $32. N_0 percent_0 N_1 percent_1 NMISS percent_missing 8.;
    set part1;
    totalobs=NMISS+N;
    N_0=totalobs-sum-NMISS;
    N_1=sum;
    percent_0=N_0/totalobs;
    percent_1=N_1/totalobs;
    percent_missing=nmiss/totalobs;
    ;
    format percent: percent12.2;
run;

@Wolverine wrote:

I'm trying to format a frequencies output dataset. The data looks like this:

varname COUNT PERCENT

20 .200000000

0 50 .500000000

1 30 .300000000

I want it to look like this:

_NAME_ 0 PERCENT_0 1 PERCENT_1 missing PERCENT_m

varname 50 .50 30 .30 20 .20

Current code:
PROC TRANSPOSE data=have out=want;
	VAR varname;
	BY COUNT PERCENT; 
RUN;