moving long data into a column name

[lmyers2]

Hello,

I'm trying to do the following data transformation but what I've tried (proc transpose, proc sql) hasn't worked.

This is current data in long format:

Patient    Visit_Count  Visit_Type

1              3                    AV

1              2                     IP

2              3                    AV

2              1                    TE

3              5                   AV

What I want is 1 row per patient with the Visit_type as the name of the column, then the number of visits as the value of that column.

Patient    AV    IP   TE  

1             3       2      .

2             3        .      1

3             5        .       .

[PeterClemmensen · Accepted solution]

data have;
input Patient Visit_Count Visit_Type $;
datalines;
1 3 AV
1 2 IP
2 3 AV
2 1 TE
3 5 AV
;

proc transpose data=have out=want;
    by Patient ;
    id Visit_Type;
    var Visit_Count;
run;

[Reeza]

---

@lmyers2 wrote:

Hello,

I'm trying to do the following data transformation but what I've tried (proc transpose, proc sql) hasn't worked.

---

PROC TRANSPOSE works fine with the ID statement. 

Showing what you've tried is helpful.

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

---

@lmyers2 wrote:

Hello,

I'm trying to do the following data transformation but what I've tried (proc transpose, proc sql) hasn't worked.

This is current data in long format:

Patient    Visit_Count  Visit_Type

1              3                    AV

1              2                     IP

2              3                    AV

2              1                    TE

3              5                   AV

What I want is 1 row per patient with the Visit_type as the name of the column, then the number of visits as the value of that column.

Patient    AV    IP   TE  

1             3       2      .

2             3        .      1

3             5        .       .

---