topic Re: update master dataset with transact datset in SAS Programming

update master dataset with transact datset

rawindar — Thu, 06 Apr 2017 10:49:10 GMT

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.

Re: update master dataset with transact datset

RW9 — Thu, 06 Apr 2017 11:17:45 GMT

You should use the specific commands for doing such a task:

https://v8doc.sas.com/sashtml/lgref/z0202975.htm

A more modern page on it:

http://support.sas.com/documentation/cdl/en/basess/68381/HTML/default/viewer.htm#p01espjvpdhfafn1u3h96ond462p.htm

Re: update master dataset with transact datset

ballardw — Thu, 06 Apr 2017 14:26:44 GMT

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.

Re: update master dataset with transact datset

rawindar — Tue, 11 Apr 2017 06:31:47 GMT

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