Transposing data

[Solly7]

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

[PaigeMiller · Accepted solution]

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 .