topic moving long data into a column name in New SAS User

moving long data into a column name

lmyers2 — Thu, 15 Apr 2021 19:02:06 GMT

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 . .

Re: moving long data into a column name

PeterClemmensen — Thu, 15 Apr 2021 19:14:24 GMT

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;

Re: moving long data into a column name

Reeza — Thu, 15 Apr 2021 19:20:46 GMT

@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        .       .