BookmarkSubscribeRSS Feed
tsureshinvites
Obsidian | Level 7

 

data sv_raw1;
input PT_ID $ Visit $ Res;
datalines;
2 4 10
2 5 15
1 1 20
1 2 40
1 3 30
2 1 30
2 2 60
2 3 50
3 1 .
3 2 40
3 3 60
3 4 70
4 1 30
4 2 20
4 3 30
4 4 35
5 1 90
5 2 91
5 3 30
5 4 40
5 5 70
6 1 .
6 2 .
6 3 30
6 4 40
1 0 10
2 0 10
3 0 10
4 0 10
5 0 10
6 0 10
;

 

Scenario:

1. Baseline visit = 2.

2. Prior baseline visit's res value ( 0,1) should populate  in BASE (Variable)

3. Post Baseline visit's res value (3+ visits) should populate in BASE (Variable) 

3. if Visit 2 value is missing then prior visit's value should populate in BASE (Variable) 

 

Output Required:

 

PT_IDVisitResBase
101010
112010
124040
133040
201010
213010
226060
235060
241060
251560
301010
31.10
324040
336040
347040
401010
413010
422020
433020
443520
501010
519010
529191
533091
544091
557091
601010
61.10
62.10
633010
644010

 

My code:

 

proc sort data=sv_raw1 out=sv1;
by PT_ID Visit Res;
run;

data baseline1;
set sv1;
retain Base;
by PT_ID Visit Res;
if first.PT_ID then Base = Res;
else if visit eq '2' then Base = Res;
else if visit not in ('2') then
run;

1 REPLY 1
Astounding
PROC Star

Because you created VISIT as character, you could run into trouble if any of the VISIT values are greater than "9".  For example, when sorting character variables, "10" sorts before "2". 

 

Assuming the VISIT values are always one digit long, you could use:

 


data baseline1;
set sv1;
retain Base;
by PT_ID Visit Res;
if visit le '2' then Base = Res;
run;

This logic also assumes that each PT_ID has an initial VISIT with a value less than "2".

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!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 246 views
  • 0 likes
  • 2 in conversation