I have a dataset with the following columns: dates, client name, jail record, jail facility name
I need to query the data in base sas as follow:
If a client has a jail record and there is a previous jail record within 30 days where the jail facility was different then i want to code it as an event for each client.
Any ideas how to do that please!
Here's a reasonable approach:
proc sort data=have;
by client date;
run;
data want;
set have;
by client;
days_lapsed = dif(date);
prior_facility = lag(facility);
if first.client=0 and days_lapsed < 30 and facility = prior_facility then event='Y';
drop days_lapsed prior_facility;
run;
This assumes that your date variable is actually a SAS date, not a character string.
Here's a reasonable approach:
proc sort data=have;
by client date;
run;
data want;
set have;
by client;
days_lapsed = dif(date);
prior_facility = lag(facility);
if first.client=0 and days_lapsed < 30 and facility = prior_facility then event='Y';
drop days_lapsed prior_facility;
run;
This assumes that your date variable is actually a SAS date, not a character string.
Sort by client name and date. Then you can use the lag() function to compare variables with values from the preceding observation.
Do you have some id such as SSN? Note that people involved in jail often have a good reason to hide their real identity and multiple arrests and/or convictions and a simple name match is likely to be insufficient. Also is Rob Smith the same as Robert Smith or Bob Smith?
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.