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

creating a variable which is based on information given between first. and last. of other var

I have a data which looks like this-

 Patient code episode code code2 P1 1 NS S P1 1 S S P1 1 S S P1 1 NS S P1 2 NS NS P1 2 NS NS P1 3 S S P2 1 S S P2 2 NS S P2 2 S S

I have patient code , episode and code and want to create a variable like code2.

Code 2 should be S if any of the rows corresponding to an episode of a patient is S.  Like for P1 , episode 1 , since middle two rows are S all rows corresponding to P1, episode 1 should show S.

How do I write a code for this?

Would first. and last. come to use here.

I am clueless.

1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

Re: creating a variable which is based on information given between first. and last. of other var

Could do something like:

```proc sort data=have;
by patient_code episode descending code;
run;
data want;
set have;
retain code2;
by patient_code episode;
if first.episode then code2=code;
run; ```

It wouldn't be in your original order, but I am guessing there is other information for that (i.e. dates/times or something).  Oh, an please post test data in the form of a datastep in future, the above is not tested.

3 REPLIES 3
Amethyst | Level 16

Re: creating a variable which is based on information given between first. and last. of other var

``````data have;
input Patient \$ 	episode	code\$;
cards;
P1 1 NS
P1 1 S
P1 1 S
P1 1 NS
P1 2 NS
P1 2 NS
P1 3 S
P2 1 S
P2 2 NS
P2 2 S
;

proc sort data=have;
by patient episode code;
run;

data want;
do until(last.episode);
set have;
by patient episode code;
if last.episode then code2=code;
end;
do until(last.episode);
set have;
by patient episode code;
output;
end;
run;

``````
Thanks,
Jag
Super User

Re: creating a variable which is based on information given between first. and last. of other var

Assuming your dataset is sorted accordingly:

``````data want;
merge
have
have (
rename=(code=code2)
where=(code2 = 'S')
)
;
by patient_code episode;
if code2 = '' then code2 = code;
run;
``````
Diamond | Level 26

Re: creating a variable which is based on information given between first. and last. of other var

Could do something like:

```proc sort data=have;
by patient_code episode descending code;
run;
data want;
set have;
retain code2;
by patient_code episode;
if first.episode then code2=code;
run; ```

It wouldn't be in your original order, but I am guessing there is other information for that (i.e. dates/times or something).  Oh, an please post test data in the form of a datastep in future, the above is not tested.

Discussion stats
• 3 replies
• 735 views
• 2 likes
• 4 in conversation