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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.