multiple visits and readings

Reply
Occasional Contributor
Posts: 6

multiple visits and readings

Hi SAS experts! I am attempting to use a flat data file to create a new dataset. Currently each patient has multiple clinical visits and multiple parameter readings for each visit. The file has been flattened so that all visits for each patient are on one row. I need to find the average reading for each parameter, but the number of readings differs by patient.  Here is an example of what I have and what I need. Any help you can provide would be greatly appreciated.

Thanks, Brandi

What I have

ID      result_lab_avg1      paramater_avg1      result_lab_avg2      paramater_avg2       result_lab_avg3      paramater_avg3

11      HGB                         157                          HGB                     157                               BNP                     245

22      BNP                          254                          HGB                     257                              BNP                      214

35      HGB                          332

94      HGB                          157                          BNP                     157                               BNP                    115

What I need

ID      BNP_AVG                HGB_AVG

11

22

35

94

Respected Advisor
Posts: 3,124

Re: multiple visits and readings

Not ideal, lots of hard coding, I hope by adding Proc Transpose or some other technique, you can make it more robust, but I don't really have the bandwidth today:

data have;

infile cards truncover;

input ID$ result_lab_avg1$ paramater_avg1 result_lab_avg2$ paramater_avg2 result_lab_avg3 $ paramater_avg3;

cards;

11 HGB 157 HGB 157 BNP 245

22 BNP 254 HGB 257 BNP 214

35 HGB 332 

94 HGB 157 BNP 157 BNP 115

;

data want;

set have;

array char _character_;

array num _numeric_;

length _r $8;

do i=1 to dim(num);

  _r=char(i+1);

  _p=num(i);

  if _r='HGB' then do; _hc+1; _hgb+_p;end;

  else if _r='BNP' then do; _bc+1; _bnp+_p; end;

end;

HGB_AVG=_hgb/_hc;

BNP_AVG=_bnp/_bc;

output;

call missing (of _Smiley Happy;

keep id HGB_AVG BNP_AVG;

run;

Haikuo

Super User
Posts: 10,500

Re: multiple visits and readings

If you import the flat file and have the variables named as your example:

You don't say which variable becomes BNP and which HGB, if I guess wrong just change .

Data want (keep=id  BNP_AVG  HGB_AVG);

     set have;

     BNP_AVG= mean (of  result_lab_avgSmiley Happy;

     HGB_AVG = mean (of paramater_avgSmiley Happy;

run;

The : are to reference all variables that start with the same characters.

Respected Advisor
Posts: 4,649

Re: multiple visits and readings

A very standard way of doing this :


data have;
length paramater_avg1-paramater_avg3 $3;
infile datalines missover;
input ID paramater_avg1 result_lab_avg1 paramater_avg2 result_lab_avg2 paramater_avg3 result_lab_avg3;
datalines;
11      HGB                         157                          HGB                     157                               BNP                     245
22      BNP                          254                          HGB                     257                              BNP                      214
35      HGB                          332
94      HGB                          157                          BNP                     157                               BNP                    115
;

data long(keep=id param result);
set have;
array p{*} $ paramater_avg:;
array v{*} result_lab_avg:;
do i = 1 to dim(p);
     param = upcase(p{i});
     result = v{i};
     if not missing(param) then output;
     end;
run;

proc sql;
create table longMean as
select id, param, mean(result) as meanResult
from long
group by id, param;
quit;

proc transpose data=longMean out=want(drop=_name_) suffix=_AVG;
by id;
var meanResult;
id param;
run;

PG

PG
Ask a Question
Discussion stats
  • 3 replies
  • 196 views
  • 0 likes
  • 4 in conversation