BookmarkSubscribeRSS Feed
rawindar
Calcite | Level 5

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.

3 REPLIES 3
ballardw
Super User

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.

rawindar
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 3 replies
  • 887 views
  • 0 likes
  • 3 in conversation