PROC TRANSPOSE one variable for across multiple variables

[mariko5797]

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.

[Reeza · Accepted solution]

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.

---