BookmarkSubscribeRSS Feed
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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

4 REPLIES 4
Tom
Super User Tom
Super User

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
Super User
Note: I've modified the subject line to be descriptive.
andreas_lds
Jade | Level 19

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

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 904 views
  • 0 likes
  • 5 in conversation