Hi,
I have a dataset which is supposed to be at the person-level, but really there are some people on multiple rows (I know because I've added an umbrella ID and there are some people on two rows with the same umbrella ID but different values of my former ID variable, ID2). My dataset has ID1 (the umbrella ID), ID2, indicators for each month (=1 if the person had an event in that month), and a number of categorical variables.
ID1 ID2 mth_200901 mth_200902...mth_201311 mth_201312 categ_1 categ_2
1 1 1 1 . . abc pqr
1 2 . . 1 1 def xyz
What I want is two things. One is simple and is just that for each value of ID1, they should have a value of 1 for each mth_ indicator as long as one of their sub-IDs (ID2) had a 1 for that month. The other is that I want the values of categ_1 and categ_2 (and the several other categorical vars) as of the row with the latest month that =1. So my final dataset would have this summary row for ID1=1.
ID1 mth_200901 mth_200902...mth_201311 mth_201312 categ_1 categ_2
1 1 1 1 1 def xyz
Any help is much appreciated.
Assuming your data set is sorted by ID1 ID2, you could use:
data want;
update have (obs=0) have;
by ID1;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.