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?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.