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

Data sample: 

IDTestVisitType
10S
11S
11U
10S
11S
11S
11S
21S
21S
20S
21S
21U
21U
21U
21U
20S
20S
20S

 

Based on the above data, I'm trying to create a variable (VarWant) that, for each ID, is a 1 if VisitType=S and Test=1 OR is a 1 if VisitType=S, Test=0, BUT the preceding VisitType=U and Test=1. For clarification, here is what I'm looking for:

 

IDTestVisitTypeVarWant
10S0
11S1
11U1
10S1
11S1
11S1
11S1
21S1
21S1
20S0
21S1
21U1
21U1
21U1
21U1
20S1
20S0
20S0

 

I know I can use a retain statement for this, but I can't figure out the logic statement that should accompany it. Thanks in advance for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

The LAG function should make this straightforward:

 

data want;
   set have;
   by id;
   prior_type = lag(VisitType);
   prior_test = lag(test);
   if first.id then call missing(prior_type, prior_test);
   VarWant = (VisitType="S" and test=1) or (VisitType="S" and test=0 and prior_type="U" and prior_test=1);
run;

View solution in original post

2 REPLIES 2
Astounding
PROC Star

The LAG function should make this straightforward:

 

data want;
   set have;
   by id;
   prior_type = lag(VisitType);
   prior_test = lag(test);
   if first.id then call missing(prior_type, prior_test);
   VarWant = (VisitType="S" and test=1) or (VisitType="S" and test=0 and prior_type="U" and prior_test=1);
run;
EpiNovice
Calcite | Level 5
This worked beautifully, thank you!!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 2 replies
  • 2028 views
  • 0 likes
  • 2 in conversation