topic Re: Join a table with another in long format by two variables in New SAS User

Join a table with another in long format by two variables

NewUsrStat — Tue, 11 Mar 2025 17:30:09 GMT

Hi guys,

suppose to have the following dataset:

data DB;
  input Admission :date09. week :$20. Count_path1 Count_path2; 
  format Admission date9.;
cards;
06JUL2014 28w2014  0   21
13JUL2014 29w2014  1   56
20JUL2014 30w2014  0   0
27JUL2014 31w2014  3   1
03AUG2014 32w2014  4   4
10AUG2014 33w2014  0   0
;

and another one:

data DB1;
  input week :$20. Pathogen :$20. Value; ;
cards;
28w2014 path1 23
29w2014 path1 1
30w2014 path2 56
31w2014 path1 67
32w2014 path2 6
33w2014 path2 2
;

Is there a way to get the following?

data DB3;
  input Admission :date09. week :$20. Count_path1 Count_path2 path1 path2; 
  format Admission date9.;
cards;
06JUL2014 28w2014  0   21  23   0
13JUL2014 29w2014  1   56  1    0
20JUL2014 30w2014  0   0   0    56
27JUL2014 31w2014  3   1   67   0
03AUG2014 32w2014  4   4   0    6
10AUG2014 33w2014  0   0   0    2
;

In other words I would like to join the two tables at week and pathogen variables.

Thank you in advance

Re: Join a table with another in long format by two variables

Quentin — Tue, 11 Mar 2025 17:39:21 GMT

Can you show the code you have tried? Curious if you tried a DATA step merge, or SQL approach? It will help people help you to see the code you have tried, along with an explanation whether you are getting errors from your code, or unexpected results.

Re: Join a table with another in long format by two variables

Tom — Tue, 11 Mar 2025 18:42:50 GMT

To turn VALUE into PATH1 or PATH2 you need to transpose the second dataset.

proc transpose data=db1 out=db1_wide(drop=_name_);
  by week;
  id pathogen;
  var value;
run;

data want ;
  merge db db1_wide;
  by week;
run;

                                     Count_    Count_
      Obs    Admission     week       path1     path2    path1    path2

       1     06JUL2014    28w2014       0        21        23        .
       2     13JUL2014    29w2014       1        56         1        .
       3     20JUL2014    30w2014       0         0         .       56
       4     27JUL2014    31w2014       3         1        67        .
       5     03AUG2014    32w2014       4         4         .        6
       6     10AUG2014    33w2014       0         0         .        2

To convert the missing values into zeros you could either add the follow lines to your merge step:

path1=sum(path1,0);
path2=sum(path2,0);

Or add a call to PROC STDIZE to fill missing values with zeros.