topic How to update a data set with information from another data set in SAS Programming

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

scb — Tue, 29 Aug 2017 03:50:24 GMT

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

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

Tom — Tue, 29 Aug 2017 04:01:05 GMT

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?

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

Reeza — Tue, 29 Aug 2017 03:50:38 GMT

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

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

andreas_lds — Tue, 29 Aug 2017 08:24:46 GMT

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;

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

ballardw — Tue, 29 Aug 2017 15:07:35 GMT

@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.