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.
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:
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: