Hi, I need help with transposing the below data..please note that each policy_id contains 5 rows.. i need to transpose the data in such a way that i remain with 1 policy_id and 5 columns corresponding to the policy_id
data have; input policy_id question & $60. answer & $60.; cards; 1 alcohol no 1 existing_cover no 1 height 1.2 1 lead_type out 1 weight 52 2 alcohol no 2 existing_cover yes 2 height 1.4 2 lead_type in 2 weight 55 3 alcohol yes 3 existing_cover yes 3 height 1.6 3 lead_type in 3 weight 60 ;
Data Want
policy_id alcohol existing_cover height lead_type weight
1 yes no 1.2 out 52
2 no yes 1.4 in 55
3 yes yes 1.6 in 60
proc transpose data=have out=want;
by policy_id;
var answer;
id question;
run;
When you start with numbers in a character variable, as in your original data set, then to quote the Rolling Stones, "you can't always get what you want", because TRANSPOSE maintains these as character strings. You will need to write additional code after PROC TRANSPOSE to convert these supposedly numeric but actually character variables to actually numeric variables .
proc transpose data=have out=want;
by policy_id;
var answer;
id question;
run;
When you start with numbers in a character variable, as in your original data set, then to quote the Rolling Stones, "you can't always get what you want", because TRANSPOSE maintains these as character strings. You will need to write additional code after PROC TRANSPOSE to convert these supposedly numeric but actually character variables to actually numeric variables .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.