topic Re: Transpose Data in SAS in SAS Programming

Transpose Data in SAS

a_zacMD — Mon, 06 Jul 2020 15:33:10 GMT

Hi SAS community,

I have a huge data file of information like this:

Current Data
ID	activity
abc123	1/22/2020
abc123	4/1/2020
abc123	3/15/2020
abc123	7/1/2020
abc123	5/10/2020
bbfr45	1/5/2020
bbfr45	4/8/2020
fff6gr	2/8/2020
fff6gr	6/8/2020
fff6gr	3/9/2020

But I want to be able to flip it so I can calculate date differences between the various points of activity. Is there a proc in SAS that will allow for this transformation?

Wanted Data
ID	Activity 1	Activity 2	Activity 3	Activity 4	Activity 5
abc123	1/22/2020	4/1/2020	3/15/2020	5/10/2020	7/1/2020
bbfr45	1/5/2020	4/8/2020
fff6gr	2/8/2020	3/9/2020	6/8/2020

Thank you in advance!

Re: Transpose Data in SAS

PaigeMiller — Mon, 06 Jul 2020 15:37:28 GMT

You don't need to flip the data to compute differences between dates. This code assumes that you have actual SAS dates and not a character string that looks like a date, and not SAS date/time values.

data want;
    set have;
    by id;
    prev_activity=lag(activity);
    if not first.id then diff=activity-prev_activity;
    drop prev_activity;
run;

Re: Transpose Data in SAS

Reeza — Mon, 06 Jul 2020 15:39:42 GMT

PROC TRANSPOSE

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/

But you can also do it in a long method, either using a PROC EXPAND or data step.

data want;
set have;
by ID;
retain

if First.ID then first_event = activity;
time_from_first = activity - first_event;

run;

Re: Transpose Data in SAS

Angel_Larrion — Thu, 09 Jul 2020 21:14:52 GMT

You can use the following code to create the table you want.

PROC TRANSPOSE data=a out=b prefix=Activity;
	 by id;
	 var activity;	
RUN;

where b is the transposed table and a is the input table.