Sequential merging

[namrata]

Hi

I have got two datasets that I need to merge based on three identifiers(sequentially) and year.

Each of the three Ids do not contain values for all the years. So, a sequential merge of the datasets based on the three Ids and year would help avoid data loss.

I have tried two ways but they have not worked. Could anybody tell me if UPDATE function is the right command to use here?Any suggestions would be very appreciated.                   

Thank you.

Namrata

[art297]

Can you provide example 'have' and 'want' datasets that show what you are trying to accomplish?

[namrata]

Arthur

Yes, I should have provided a sample of my datasets. I want to attach a sample of the two datasets - have 1 & 2 but cannot find the attach option :smileyconfused:.

I am following another paper which does the match sequentially using cusip, name and ID. The reason for this sequential match is to get the maximum match possible.

Have1

Year        Cusip1           Name          ID       Var

1990      1                     app            123       4

1991       1                    app              123      5

1990       2                     bpp             321      6     

1991       2                     bpp             321      7

Have2

Year        Cusip1           Name          ID       Var

1990       1                     app            123       4

1991       1                     ----              ----        5

1990        ---                  bpp             ----        6     

1991       2                     ----              321       7

Want

Year        Cusip1           Name          ID       Var

1990      1                     app            123       4

1991       1                    app             123       5

1990       2                     bpp            321       6     

1991       2                   bpp           321       7

So, in the want set, I do not want to lose any values of the VAR variable since any particular identifier is missing in a year. I want to be ale to do this using alternative identifiers for years in which the initial identifier is missing.

[art297]

Not sure I understand what you are trying to do. If I do, then the following might be one way of doing it:

data have1;

  input Year Cusip1 Name $ ID Var;

  cards;

1990      1                    app            123      4

1991      1                    app            123      5

1990      2                    bpp            321      .

1991      2                    bpp            321      6

;

data have2;

  input Year Cusip1 Name $ ID Var;

  cards;

1990       1                  app            123     4

1991       1                  .              .       5

1990       .                  bpp            .       6    

1991       2                  .              321     7

;

data have2;

  set have1 (keep= Cusip1 Name ID year);

  set have2 (keep=Var);

run;

data want;

  update have1 have2;

  by Cusip1 Name ID year;

run;

[Ksharp]

If these two datasets have the exact same number of obs :

data have1;
  input Year Cusip1 Name $ ID Var;
  cards;
1990      1                    app            123      4
1991      1                    app            123      5
1990      2                    bpp            321      .
1991      2                    bpp            321      6
;
 
data have2;
  input Year Cusip1 Name $ ID Var;
  cards;
1990       1                  app            123     4
1991       1                  .              .       5
1990       .                  bpp            .       6    
1991       2                  .              321     7
;
data have1; 
 set have1;
 n+1;
run;
data have2;
 set have2;
 n+1;
run;
data want(drop=n);
 update have1 have2;
 by n;
run;

Xia Keshan