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.
... View more
