topic Re: Sequential merging in SAS Procedures

Sequential merging

namrata — Sun, 12 Oct 2014 06:55:54 GMT

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

Re: Sequential merging

art297 — Sun, 12 Oct 2014 16:08:01 GMT

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

Re: Sequential merging

namrata — Sun, 12 Oct 2014 20:21:49 GMT

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.

Re: Sequential merging

art297 — Sun, 12 Oct 2014 21:00:07 GMT

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;

Re: Sequential merging

Ksharp — Mon, 13 Oct 2014 12:49:35 GMT

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