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!
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;
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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: