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?
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.