Transposing data

Solly7 — Wed, 09 Jun 2021 10:32:30 GMT

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

Re: Transposing data

PaigeMiller — Wed, 09 Jun 2021 11:41:09 GMT

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 .

topic Transposing data in SAS Programming

Transposing data

Re: Transposing data