BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lordy
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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.

View solution in original post

4 REPLIES 4
Astounding
PROC Star

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.

Lordy
Obsidian | Level 7
Thanks - I will use this approach.
ballardw
Super User

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?

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 749 views
  • 1 like
  • 4 in conversation