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?
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.