Created Wide Data from Long Data, But Some Values Were Lost

[dwrightii]

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

 

[MarkDawson · Accepted solution]

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.

[dwrightii]

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!