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.
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.