Hi all;
I ran the code below using OnDemand 9.4 (TS1M4), but the first three observations did not pull over the values (see screenshot). I double checked to make sure the values themselves weren't missing. There isn't anything that stands out to me in the log, either. I'm not sure how to correct this; any ideas?
DATA WORK.Vitals_ST (LABEL = 'Vital Signs');
LENGTH SSN $11
VisitDt 8
HtIn 8
WtLb 8
SBP 8
DBP 8;
SET STATE_VITALS_0 - STATE_VITALS_4 (RENAME = (SSN = SSN_Old));
SSN = COMPRESS(SSN_Old, '.');
RETAIN HtIn WtLb SBP DBP;
LABEL SSN = "Social Security Number"
VisitDt = "Visit Date"
HtIn = "Height (In)"
WtLb = "Weight (Lb)"
SBP = "Systolic BP (mmHg)"
DBP = "Diastolic BP (mmHg)";
IF Measure = 'Height (In)' THEN HtIn = Value;
ELSE IF Measure = 'Weight (Lb)' THEN WtLb = Value;
ELSE IF Measure = 'Systolic BP' THEN SBP = Value;
ELSE DBP = Value;
VisitDt = ApptDate;
First = SUBSTR (SSN, 1, 3);
Middle = SUBSTR (SSN, 4, 2);
Last = SUBSTR (SSN, 6);
SSN = CATS (First,'-',Middle,'-',Last);
SSN_Old = SSN;
DROP Measure VALUE First Middle Last;
FORMAT VisitDt DATE9.;
RUN;
PROC SORT DATA = WORK.Vitals_ST;
BY SSN VisitDt;
RUN;
PROC PRINT DATA = WORK.Vitals_ST LABEL;
VAR SSN VisitDt HtIn WtLb SBP DBP;
RUN;
PROC CONTENTS DATA = WORK.Vitals_ST ORDER = VARNUM;
RUN;
*****LOG*****;
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
70
71 DATA WORK.Vitals_ST (LABEL='Vital Signs');
72 LENGTH SSN$11
73 VisitDt8
74 HtIn8
75 WtLb8
76 SBP8
77 DBP8;
78
79 SET UTAH_VITALS_0 - UTAH_VITALS_4 (RENAME =(SSN= SSN_Old));
80 SSN = COMPRESS(SSN_Old, '.');
81
82 RETAIN HtIn WtLb SBP DBP;
83
84 LABELSSN= "Social Security Number"
85 VisitDt= "Visit Date"
86 HtIn= "Height (In)"
87 WtLb= "Weight (Lb)"
88 SBP= "Systolic BP (mmHg)"
89 DBP= "Diastolic BP (mmHg)";
90
91 IF Measure = 'Height (In)' THENHtIn= Value;
92 ELSE IF Measure = 'Weight (Lb)' THENWtLb= Value;
93 ELSE IF Measure = 'Systolic BP' THENSBP= Value;
94 ELSE DBP = Value;
95
96 VisitDt= ApptDate;
97 First= SUBSTR (SSN, 1, 3);
98 Middle= SUBSTR (SSN, 4, 2);
99 Last= SUBSTR (SSN, 6);
100 SSN= CATS (First,'-',Middle,'-',Last);
101 SSN_Old=SSN;
102
103 DROP Measure VALUE First Middle Last;
104
105 FORMATVisitDt DATE9.;
106 RUN;
NOTE: There were 748 observations read from the data set WORK.UTAH_VITALS_0.
NOTE: There were 680 observations read from the data set WORK.UTAH_VITALS_1.
NOTE: There were 692 observations read from the data set WORK.UTAH_VITALS_2.
NOTE: There were 648 observations read from the data set WORK.UTAH_VITALS_3.
NOTE: There were 588 observations read from the data set WORK.UTAH_VITALS_4.
NOTE: The data set WORK.VITALS_ST has 3356 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.01 seconds
memory 2433.50k
OS Memory 33724.00k
Timestamp 11/11/2020 11:06:11 PM
Step Count 431 Switch Count 2
Page Faults 0
Page Reclaims 349
Page Swaps 0
Voluntary Context Switches 9
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 528
107
108 PROC SORT DATA = WORK.Vitals_ST;
109 BYSSN VisitDt;
110
111 RUN;
NOTE: There were 3356 observations read from the data set WORK.VITALS_ST.
NOTE: The data set WORK.VITALS_ST has 3356 observations and 8 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 1722.81k
OS Memory 33204.00k
Timestamp 11/11/2020 11:06:12 PM
Step Count 432 Switch Count 2
Page Faults 0
Page Reclaims 143
Page Swaps 0
Voluntary Context Switches 11
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 520
112
113 PROC PRINT DATA = WORK.Vitals_ST LABEL;
114 VARSSN VisitDt HtIn WtLb SBP DBP;
115
116 RUN;
NOTE: There were 3356 observations read from the data set WORK.VITALS_ST.
NOTE: PROCEDURE PRINT used (Total process time):
real time 4.84 seconds
user cpu time 4.85 seconds
system cpu time 0.00 seconds
memory 3121.78k
OS Memory 32680.00k
Timestamp 11/11/2020 11:06:16 PM
Step Count 433 Switch Count 0
Page Faults 0
Page Reclaims 151
Page Swaps 0
Voluntary Context Switches 0
Involuntary Context Switches 7
Block Input Operations 0
Block Output Operations 2432
117
118 PROC CONTENTS DATA = WORK.Vitals_ST ORDER = VARNUM;
119
120 RUN;
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.05 seconds
user cpu time 0.05 seconds
system cpu time 0.00 seconds
memory 1698.68k
OS Memory 33196.00k
Timestamp 11/11/2020 11:06:16 PM
Step Count 434 Switch Count 0
Page Faults 0
Page Reclaims 135
Page Swaps 0
Voluntary Context Switches 6
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 40
121
122 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
133
It's difficult to know for sure without seeing the input data, but I believe the issue is related to this code
IF Measure = 'Height (In)' THEN HtIn = Value;
ELSE IF Measure = 'Weight (Lb)' THEN WtLb = Value;
ELSE IF Measure = 'Systolic BP' THEN SBP = Value;
ELSE DBP = Value;
For the first row read, it would appear that MEASURE was 'Height (In)', and so HtIn was given a value. Because it had that value, all other columns WtLB, SBP, and DBP were not set.
For the second row read, it would appear that MEASURE was 'Weight (Lb)', and so WtIb was given a value. Because it had that value, all other columns WtLB, SBP, and DBP were not set, however because HtIn is retained, and kept the value set when the the first row was processed.
It would appear that you are reading 4 rows to get all 4 values, so you need to watch out for when the Visit Date changes, as you can see in your final table that values from Row 4, are also in row 5, and might not be what you want.
Adding a BY statement and resetting the retained values might help, as demonstrated in this example...
data sample ;
input ssn day type $ value ;
datalines ;
1 1 height 1
1 1 weight 2
1 1 BP 3
1 2 height 1
1 2 weight 2
1 2 BP 3
2 1 height 1
2 1 weight 2
2 1 BP 3
; run ;
data result ;
set sample ;
* As your data already seems to have some order, you might not need to sort it,
however NOTSORTED might be needed of it is only grouped ;
by ssn day notsorted ;
retain height weight bp ;
if first.day then call missing (height,weight,bp) ;
if type = "height" then height = value ;
else if type = "weight" then weight = value ;
else if type = "BP" then bp = value ;
run ;
You could also add a IF LAST.DAY ; before the RUN ; to only keep the last row for each group.
It's difficult to know for sure without seeing the input data, but I believe the issue is related to this code
IF Measure = 'Height (In)' THEN HtIn = Value;
ELSE IF Measure = 'Weight (Lb)' THEN WtLb = Value;
ELSE IF Measure = 'Systolic BP' THEN SBP = Value;
ELSE DBP = Value;
For the first row read, it would appear that MEASURE was 'Height (In)', and so HtIn was given a value. Because it had that value, all other columns WtLB, SBP, and DBP were not set.
For the second row read, it would appear that MEASURE was 'Weight (Lb)', and so WtIb was given a value. Because it had that value, all other columns WtLB, SBP, and DBP were not set, however because HtIn is retained, and kept the value set when the the first row was processed.
It would appear that you are reading 4 rows to get all 4 values, so you need to watch out for when the Visit Date changes, as you can see in your final table that values from Row 4, are also in row 5, and might not be what you want.
Adding a BY statement and resetting the retained values might help, as demonstrated in this example...
data sample ;
input ssn day type $ value ;
datalines ;
1 1 height 1
1 1 weight 2
1 1 BP 3
1 2 height 1
1 2 weight 2
1 2 BP 3
2 1 height 1
2 1 weight 2
2 1 BP 3
; run ;
data result ;
set sample ;
* As your data already seems to have some order, you might not need to sort it,
however NOTSORTED might be needed of it is only grouped ;
by ssn day notsorted ;
retain height weight bp ;
if first.day then call missing (height,weight,bp) ;
if type = "height" then height = value ;
else if type = "weight" then weight = value ;
else if type = "BP" then bp = value ;
run ;
You could also add a IF LAST.DAY ; before the RUN ; to only keep the last row for each group.
Thank you for the reply, and I apologize for the delayed response. I added in the suggestions you gave, but it only partially worked. However, I saw that the BY statement was coded SSN, but the name change from SSN_Old to SSN hadn't happened yet (though I thought it would have by then). Once I changed the BY statement from SSN to SSN_Old, it ran correctly and without errors. The combination of this observation and your suggestions helped make this code run. Thank you again!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.