DATA Step, Macro, Functions and more

update master dataset with transact datset

Reply
Contributor
Posts: 70

update master dataset with transact datset

data master;

format date_key date9.;

input DATE_KEY date9. BU_KEY ORD_NUM ORD_LINE_NUM NOTE_NUM;

datalines;

11MAR2017 213 304534 1 -2

11MAR2017 213 304534 2 -2

11MAR2017 213 304534 3 -2

11MAR2017 213 304534 4 -2

11MAR2017 213 304534 5 -2

11MAR2017 213 304534 6 -2

11MAR2017 213 304534 7 -2

25Mar2017 226 5466297352 0001 -2

25Mar2017 226 5466292861 2 3.51329E+13

run;

proc sort data=master;

by date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM;

run;

data transact;

format date_key date9.;

input DATE_KEY date9. BU_KEY ORD_NUM ORD_LINE_NUM NOTE_NUM;

datalines;

11MAR2017 213 304534 1 928258

11MAR2017 213 304534 2 928258

11MAR2017 213 304534 3 928258

11MAR2017 213 304534 4 928258

11MAR2017 213 304534 5 928258

11MAR2017 213 304534 6 928258

25Mar2017 226 5466297352 0001 35132940826849

25Mar2017 226 5466292861 3 3.51329E+13

run;

proc sort data=transact;

by date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM;

run;

data master;

merge master(in=a) transact(in=b);

by date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM;

if a=1 or b=1;

run;

 

I have a master datset and transact dataset .

Masterdataset have millions of rows at fist comes with note_num=-2 and other xxx values.
Next update it comes with other any updated values with note_num=111. for the same ord_num
 and other new Ord_nums will come.

The problem here is
case1:if the same note_num(222) come with updated(222) value(note num same and other values change for remaining coloumns) it will replace in merge.It's fine in this case.
case2:if the new ord_num comes then in left join it adds to master.It's fine here.
case3.If the note_num come with '-2' new or old it will be ok in merege again.

case4:Master dataset contains note_num = -2 for ord_num yyy
but the same ord_num(yyy) with same_ord_line_num(1 or 2 or 3) come with new note_num.

in this case it should replace -2 with XXX.

I can not delete note_num ='-2' from master because few other '-2' rows will be there which is not coming in transact datset.

but my final table im getting both -2 and xxx rows.

 

I need a final o/p as below.

 

date_key

BU_KEY

ORD_NUM

ORD_LINE_NUM

NOTE_NUM

11Mar2017

213

304534

1

928258

11Mar2017

213

304534

2

928258

11Mar2017

213

304534

3

928258

11Mar2017

213

304534

4

928258

11Mar2017

213

304534

5

928258

11Mar2017

213

304534

6

928258

11Mar2017

213

304534

7

-2

25Mar2017

226

5466292861

2

3.51329E+13

25Mar2017

226

5466292861

3

3.51329E+13

25Mar2017

226

5466297352

1

3.51329E+13

 

 

thank you in advance.

 

Regards,

Rawi.

Super User
Super User
Posts: 7,392

Re: update master dataset with transact datset

[ Edited ]
Super User
Posts: 10,466

Re: update master dataset with transact datset

I strongly suggest that you do not use this structure until you have all concerns addressed.

data master;

merge master(in=a)

      transact(in=b);

 

As replacing the master set in place means that if you have a problem you will likely have a much harder time getting back to the original master.

Contributor
Posts: 70

Re: update master dataset with transact datset

Hi

 

Thank you for your suggestion

 

the problem here is i have to use all these variables in by statement since it is the primary key and it will get unique values.

.date_key bu_key ORD_NUM ORD_LINE_NUM NOTE_NUM.

 

But when transact getting updated value for examle Note_NUM=52163 where -2 in master datset.

 

It can not update since im using NOTE_NUM in by variable list.

 

finally i get two rows in SAS datset as per my code which is having -2 and updated value both

 

Again if i remove NOTE_NUM in by variable combination i can not meet othet scenarios.

 

Please suggest how can i replace the entire row which is having -2 in master and coming with updatde value in transact datset based on same combination of by variables.

 

Thank you in adavance.

 

Regards,

Rawi

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