Compute ratio from two database for a given date

[FlorianM]

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

[sbxkoenk]

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

[sbxkoenk · Accepted solution]

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

[FlorianM]

Thanks a lot, it works !

Best

Florian