Help using Base SAS procedures

Sequential merging

Reply
Contributor
Posts: 52

Sequential merging

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

PROC Star
Posts: 7,468

Re: Sequential merging

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

Contributor
Posts: 52

Re: Sequential merging

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.

PROC Star
Posts: 7,468

Re: Sequential merging

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;

Super User
Posts: 10,023

Re: Sequential merging

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

Ask a Question
Discussion stats
  • 4 replies
  • 325 views
  • 0 likes
  • 3 in conversation