BookmarkSubscribeRSS Feed
namrata
Fluorite | Level 6

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

4 REPLIES 4
art297
Opal | Level 21

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

namrata
Fluorite | Level 6

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
Opal | Level 21

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1215 views
  • 0 likes
  • 3 in conversation