topic Re: What is the right way to use arrays for merging data? in New SAS User

What is the right way to use arrays for merging data?

cheenaChuks — Sun, 07 Apr 2019 22:23:55 GMT

Hello everyone,

I am trying to create a new, temporary SAS dataset (MultSBP) where the systolic blood pressures for each patient are contained in a single observation, with the variable names SBP1–SBP5. SBP1 is the systolic blood pressure at Time=1, SBP2 is the systolic blood pressure at Time=2, etc. I am trying to do this using a DATA step and all observations should contain 5 observations in the final dataset. However, I do not seem to be getting the logic right. It shows 18 values instead of 5 and it seems to be repeating the patient ids. I just want to output 5 patients with SBP values for each time. Please, could someone take a look at my code and output? I have included a dataset for which to get the values.

data SBPvalues ; *initial dataset;
   input PTID Time SystolicBP ;
datalines;
7 3 138 
7 1 144 
7 4 126 
3 3 132 
3 1 142 
3 4 116 
3 2 118
4 1 156
4 2 148
4 3 133
4 4 129
4 5 120
1 2 122
2 2 104
2 3 108
2 1 112
1 3 .
1 5 130
;
data MultSBP; *new dataset should have 5 observations 
	set SBPvalues;
	array SBParray{5} SBP1 SBP2 SBP3 SBP4 SPB5;
		do i = 1 to 5;
		if time = i then SBParray{i}=SystolicBP;
		end;
		drop i;
run;

Re: What is the right way to use arrays for merging data?

novinosrin — Sun, 07 Apr 2019 23:18:55 GMT

Can you please post a sample of your expected output?

Re: What is the right way to use arrays for merging data?

cheenaChuks — Mon, 08 Apr 2019 01:53:01 GMT

Please see the attached doc file with the output using a sample code. I am trying to reverse the outputs. This is a sample code so that instead of multiple IDs of the same person occurring at different times. It shows one ID for each person and multiple entries for SBP values on one row.

data HGBs ;
input PTID HGB1-HGB4 ;
datalines; 
1 15.4 10.2 15.1 .
2 10.2 . 17.5 9.4
3 11.3 . 12.2 .
4 . 14.6 . 9.9
;
run;
proc print data = HGBs;run;
title "Creating SingleHGBs";
/*Reconstructing dataset from one observation to many observation per patient*/
data SingleHGBs;
	set HGBs;
	array HGB_array[4] HGB1-HGB4;
	array dys{4} $3 _temporary_ ('mon', 'tue', 'wed', 'thu') ;
	do day = 1 to 4;
			HG = HGB_array[day];
			Days = dys[day];
	if(HGB_array[day] ne null) then		output;
	end;
	keep ptid hg Days;
run;
proc print data = SingleHGBs;
run;

Re: What is the right way to use arrays for merging data?

Tom — Mon, 08 Apr 2019 03:47:35 GMT

If you only want one observation per patient then tell SAS to only write it that way.

Here is a simple way that uses a DO loop to read all of the observations for a patient in one iteration of the data step.

data MultSBP; 
  do until(last.ptid);
    set SBPvalues;
    by ptid;
    array SBP [5];
    if time in (1:5) then SBP[time]=SystolicBP;
    else if not missing(time) then put 'Invalid TIME value. ' ptid= time=;
  end;
  drop SystolicBP;
run;

Re: What is the right way to use arrays for merging data?

cheenaChuks — Mon, 08 Apr 2019 20:33:06 GMT

Thanks for your suggestion but it did not work. I keep getting the error that: BY variables are not properly sorted on data set WORK.SBPVALUES.
last.ptid=0 PTID=7 Time=4 SystolicBP=126 FIRST.PTID=0 SBP1=144 SBP2=. SBP3=138 SBP4=. SBP5=.
_ERROR_=1 _N_=1

Re: What is the right way to use arrays for merging data?

Tom — Mon, 08 Apr 2019 21:18:21 GMT

If the data is not sorted into groups then you are going to have a very hard time collapsing to one record per group.

Use PROC SORT to get the data in order.

If you KNOW that the data is grouped by PTID, put the groups are not sorted, then add the NOTSORTED keyword to the BY statement.

Re: What is the right way to use arrays for merging data?

cheenaChuks — Mon, 08 Apr 2019 22:32:04 GMT

So I tried again using proc transpose.This gives my desired output.

proc sort data=SBPvalues; by PTID;run;
proc transpose data=SBPvalues prefix=SBP out=MultSBP (drop=_NAME_);
by PTID;
id time;
var SystolicBP;
run;
proc print data=MultSBP; var PTID SBP1 SBP2 SBP3 SBP4 SBP5; run;
run;