PROC TRANSPOSE one variable for across multiple variables

mariko5797 — Wed, 18 Aug 2021 16:39:37 GMT

I want to make my data wide format while keeping the count (CNT) and percent (PCT) as columns.

Data Have >> Want

Code Attempted

/* attempt 1*/
proc transpose data = CohortA out = CohortA_wide prefix = A_;
 var CNT PCT;
 by VAR;
 id BMI;
run;

/* attempt 2*/
proc transpose data = CohortA out = CohortA_wide prefix = A_;
 by VAR;
 id BMI;
run;

The issue with attempt 1 is that CNT and PCT become categories within the same column, which is not what I want.

The issue with attempt 2 is that only CNT is shown.

Is the only way to PROC TRANSPOSE twice and then merge? Or is there a way with a single PROC TRANSPOSE?

Thank you in advance.

Re: PROC TRANSPOSE one variable for across multiple variables

Reeza — Wed, 18 Aug 2021 16:46:14 GMT

Within PROC TRANSPOSE the only solution is to transpose twice and then merge, or transpose twice, one to a super long format then to a wide format.

You can do it in a single data step though.

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd

@mariko5797 wrote:

I want to make my data wide format while keeping the count (CNT) and percent (PCT) as columns.

Data Have >> Want

Code Attempted
/* attempt 1*/
proc transpose data = CohortA out = CohortA_wide prefix = A_;
 var CNT PCT;
 by VAR;
 id BMI;
run;

/* attempt 2*/
proc transpose data = CohortA out = CohortA_wide prefix = A_;
 by VAR;
 id BMI;
run;
The issue with attempt 1 is that CNT and PCT become categories within the same column, which is not what I want.

The issue with attempt 2 is that only CNT is shown.

Is the only way to PROC TRANSPOSE twice and then merge? Or is there a way with a single PROC TRANSPOSE?

Thank you in advance.

topic PROC TRANSPOSE one variable for across multiple variables in SAS Programming

PROC TRANSPOSE one variable for across multiple variables

Re: PROC TRANSPOSE one variable for across multiple variables