topic Re: Compute ratio from two database for a given date in SAS Programming

Compute ratio from two database for a given date

FlorianM — Fri, 08 Mar 2024 19:39:16 GMT

Hello everyone,

I have two databases.
The first one contains an ID, several "date" variables (date_t1, date_t2, etc.) and several "height" variables (height_t1, height_t2, etc.). For example, date_t1 corresponds to the measurement date of the height specified in height_t1, and the same for t2, and so on.
The second contains an ID, several "date" variables (date_t1, date_t2, etc.) and several "weight" variables (weight_t1, weight_t2, etc.). For example, date_t1 corresponds to the date of measurement of the weight specified in weight_t1, idem for t2, and so on.

Here's what the databases look like:

1st database :

ID date_t1 date_t2 date_t3 date_t4 height_t1 height_t2 height_t3 height_t4

1 2nov23 1jan24 3feb24 15apr24 150 152 150 151

2 12dec23 18feb24 5apr24 19jun24 170 171 170 170

3 4oct23 15feb24 16apr24 7jun24 180 178 179 182

2nd database :

ID date_t1 date_t2 date_t3 date_t4 weight_t1 weight_t2 weight_t3 weight_t4

1 2nov23 3feb24 15apr24 7oct24 50 52 57 60

2 12dec23 18feb24 19jun24 5jul24 80 74 78 69

3 4oct23 15feb24 16apr24 7jun24 102 103 104 102

I want to calculate the height/weight ratio from these two databases for a given date. The peculiarity is that the values of the variable "dates" in the first database don't necessarily correspond to the values of the variable "dates" in the second database. For example, date_t3 in the first database corresponds to date_t2 in the second database.

Can you help me to solve this problem? To get a table where there would be only height and weight measurements taken on the same date? (date_t2 would be the same for height and weight, the same for date_t3, etc.).

Best,

Florian

Re: Compute ratio from two database for a given date

sbxkoenk — Mon, 11 Mar 2024 17:12:35 GMT

Hello,
I haven't checked for quality. Just did some quick coding. Please check if this is what you need ...

data height;
infile cards delimiter='|';
format date_: date9.;
input ID date_t1:date9. date_t2:date9. date_t3:date9. date_t4:date9. 
      height_t1 height_t2 height_t3 height_t4;
cards;
1 | 2nov23 |  1jan24 |  3feb24 | 15apr24 |  150  | 152 | 150  | 151
2 |12dec23 | 18feb24 |  5apr24 | 19jun24 |  170  | 171 | 170  | 170
3 | 4oct23 | 15feb24 | 16apr24 | 7jun24  |  180  | 178 | 179  | 182
;
run;
data weight;
infile cards delimiter='|';
format date_: date9.;
input ID date_t1:date9. date_t2:date9. date_t3:date9. date_t4:date9. 
      weight_t1 weight_t2 weight_t3 weight_t4;
cards;
1 | 2nov23 |  3feb24 | 15apr24 | 7oct24  |    50 |  52 |  57  |  60
2 |12dec23 | 18feb24 | 19jun24 | 5jul24  |    80 |  74 |  78  |  69
3 | 4oct23 | 15feb24 | 16apr24 | 7jun24  |   102 | 103 | 104  | 102
;
run;
data height_tp(drop=i date_t: height_t:);
 set height;
 array datum(4)   date_t1 - date_t4;
 array hh(4)    height_t1 - height_t4;
 do i=1 to dim(datum);
  date=datum(i);
  height=hh(i);
  output;
 end;
 format date date9.;
run;
data weight_tp(drop=i date_t: weight_t:);
 set weight;
 array datum(4)   date_t1 - date_t4;
 array ww(4)    weight_t1 - weight_t4;
 do i=1 to dim(datum);
  date=datum(i);
  weight=ww(i);
  output;
 end;
 format date date9.;
run;

data want;
 merge height_tp(in=hh) weight_tp(in=ww);
 by ID date;
 if NOT (hh and ww) then delete;
 height_div_weight_ratio=height/weight;
run;
/* end of program */

Koen

Re: Compute ratio from two database for a given date

sbxkoenk — Mon, 11 Mar 2024 17:22:53 GMT

Sorry, ... something wrong with (in)formats of the dates.
I think this is better:

data height;
infile cards delimiter='|';
informat date_t1 date_t2 date_t3 date_t4 date9.;
format   date_t: date9.;
input ID date_t1 date_t2 date_t3 date_t4 
      height_t1 height_t2 height_t3 height_t4;
cards;
1 | 2nov2023 |  1jan2024 |  3feb2024 | 15apr2024 |  150  | 152 | 150  | 151
2 |12dec2023 | 18feb2024 |  5apr2024 | 19jun2024 |  170  | 171 | 170  | 170
3 | 4oct2023 | 15feb2024 | 16apr2024 |  7jun2024 |  180  | 178 | 179  | 182
;
run;
data weight;
infile cards delimiter='|';
informat date_t1 date_t2 date_t3 date_t4 date9.;
format   date_t: date9.;
input ID date_t1 date_t2 date_t3 date_t4 
      weight_t1 weight_t2 weight_t3 weight_t4;
cards;
1 | 2nov2023 |  3feb2024 | 15apr2024 | 7oct2024  |    50 |  52 |  57  |  60
2 |12dec2023 | 18feb2024 | 19jun2024 | 5jul2024  |    80 |  74 |  78  |  69
3 | 4oct2023 | 15feb2024 | 16apr2024 | 7jun2024  |   102 | 103 | 104  | 102
;
run;
data height_tp(drop=i date_t: height_t:);
 set height;
 array datum(4)   date_t1 - date_t4;
 array hh(4)    height_t1 - height_t4;
 do i=1 to dim(datum);
  date=datum(i);
  height=hh(i);
  output;
 end;
 format date date9.;
run;
data weight_tp(drop=i date_t: weight_t:);
 set weight;
 array datum(4)   date_t1 - date_t4;
 array ww(4)    weight_t1 - weight_t4;
 do i=1 to dim(datum);
  date=datum(i);
  weight=ww(i);
  output;
 end;
 format date date9.;
run;

data want;
 merge height_tp(in=hh) weight_tp(in=ww);
 by ID date;
 if NOT (hh and ww) then delete;
 height_div_weight_ratio=height/weight;
run;
/* end of program */

Koen

Re: Compute ratio from two database for a given date

FlorianM — Wed, 03 Apr 2024 19:22:32 GMT

Thanks a lot, it works !

Best

Florian