How to update a data set with information from another data set

[scb]

May I know how to update A2 dataset with info from A1? A2 dataset are sales done by “level A2” and different team.  The A2 dataset should be updated with id of ‘Level A1’  from A1 dataset (and must from the same team)

data a1;

input team $ level $ id;

cards;

T1 A1 1110

T1 A2 2220

T1 A2 2221

T1 A2 2222

T2 A1 6721

T2 A2 8881

T2 A2 7910

;

run;

data a2;

input id ref $ amount ;

cards;

2221 abc 110

2220 abc 111

2221 abd 7819

2222 abd 673

8881 abd 27372

8881 bbc 8183

7910 bbd 373

;

run;

Desired result:

id ref amount

1110 abc 110

1110 abc 111

1110 abd 7819

1110 abd 673

6721 abd 27372

6721 bbc 8183

6721 bbd 373

[Tom]

Can you explain in more detail how you think those two input datasets can be combined to produce the output dataset you want?

For example why are the first two values of AMOUNT from dataset A2 combined with the first value of ID from dataset A1?

[Reeza]

Note: I've modified the subject line to be descriptive.

[andreas_lds]

Not sure that i understood the logic completely. a2.id refers to a1.id. a2.id has to be replaced by the first a1.id of the corresponding team/level-group. Try this

data work.idfmt;
   set work.a1;

   by team notsorted;

   length start label 8;
   retain label 0 FmtName 'id_fmt' type 'N';

   if first.team then do;
      label = id;
   end;

   start = id;

   keep start label fmtname type;
run;

proc format cntlin=work.idfmt;


proc print data=work.a2;
   format id id_fmt.;
run;

[ballardw]

---

@scb wrote:

May I know how to update A2 dataset with info from A1? A2 dataset are sales done by “level A2” and different team.  The A2 dataset should be updated with id of ‘Level A1’  from A1 dataset (and must from the same team)

data a1;

input team $ level $ id;

cards;

T1 A1 1110

T1 A2 2220

T1 A2 2221

T1 A2 2222

T2 A1 6721

T2 A2 8881

T2 A2 7910

;

run;

data a2;

input id ref $ amount ;

cards;

2221 abc 110

2220 abc 111

2221 abd 7819

2222 abd 673

8881 abd 27372

8881 bbc 8183

7910 bbd 373

;

run;

Desired result:

id ref amount

1110 abc 110

1110 abc 111

1110 abd 7819

1110 abd 673

6721 abd 27372

6721 bbc 8183

6721 bbd 373

---

You used "sales" and "team" to describe the problem but do not link either of those to a variable in your data.