Left Join/ Update Problem

[sanjaymane7]

Hi,

I have two dataset. Each datasets have 2 Cr line items. I have already done indexing in both dataset on TRAN_ID. I am trying to fill blank details in first dataset. However, query is executing since 24 hours. Don't know where is the problem? Please help in this matter.

proc sql;

update proc.oneview_4 A

set MTH

=(select MTH FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where MTH=' ';

update proc.oneview_4 A

set POLICYNO

=(select POLICYNO FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where POLICYNO=' ';

update proc.oneview_4 A

set Endt_No

=(select Endt_No FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Endt_No=' ';

update proc.oneview_4 A

set Agent_Code

=(select Agent_Code FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Agent_Code=' ';

run;

[PaigeMiller]

Maybe your PROC SQL has completed.

You need to end PROC SQL with a QUIT; and not a RUN;

If you end PROC SQL with a RUN; the way you did, it will appear to continue to run forever (or until you stop it).

[sanjaymane7]

I done necessary changes as suggested but it's still running since 10 hrs. 

[PaigeMiller]

Show us the code you are using now.

How many records (approximately) in these datasets or databases in question?

[sanjaymane7]

Hi,

I have 2 Cr records in both datasets. 

proc sql;

update proc.oneview_4 A

set MTH

=(select MTH FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where MTH=' ';

update proc.oneview_4 A

set POLICYNO

=(select POLICYNO FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where POLICYNO=' ';

update proc.oneview_4 A

set Endt_No

=(select Endt_No FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Endt_No=' ';

update proc.oneview_4 A

set Agent_Code

=(select Agent_Code FROM proc.details1 B WHERE A.TRAN_ID = B.TRAN_ID) where Agent_Code=' ';

quit;

[Kurt_Bremser]

For those who live outside of the Indian subcontinent: 1 Cr equals 10 million.

I would not use SQL for this.

data proc.oneview_4_new;
merge
  proc.oneview_4 (in=a)
  proc.details1 (
    in=b
    keep=tran_id mth policyno endt_no agent_code
    rename=(
      tran_id=_tran_id
      mth=_mth
      policyno=_policyno
      endt_no=_endt_no
      agent_code=_agent_code
    )
  )
;
by id;
if a;
tran_id = coalescec(tran_id,_tran_id);
mth = coalescec(mth,_mth);
policyno = coalescec(policy_no,_policy_no);
endt_no = coalescec(endt_no,_endt_no);
agent_code = coalescec(agent_code,_agent_code);
drop _:;
run;

I assume that tran_id is unique in both datasets, and that they are sorted by it.

[sanjaymane7]

Hi,

I am getting the following error - "ERROR: BY variable TRAN_ID is not on input data set PROC.DETAILS1."

But this field is available in both dataset. Please suggest

[Kurt_Bremser · Accepted solution]

Run a PROC CONTENTS on the dataset to reveal the variables in it.

When SAS says a variable is not there, while you say different, I trust SAS.

[sanjaymane7]

Hi,

Thanks for reply. Can you please help me again on this topic. I want to fill blank value in test1 from test2.

I have code as well and it's running properly. However, it doesn't work on 10 millions dataset. 

dataset name = test1

Obs	TRAN_ID	MTH	POLICYNO	Endt_No
1	APRIL-19-MANUAL COINS-4	APR-2019	1517652212051195	32009
2	APRIL-19-MANUAL COINS-5
3	APRIL-19-MANUAL-1	APR-2019	140521823120084451	2300001
4	APRIL-19-MANUAL-14
5	APRIL-19-MANUAL-15
6	APRIL-19-MANUAL-1000	APR-2019	140521823120078959	2300001
7	APRIL-19-MANUAL-10000
9	APRIL-19-MANUAL-10001	APR-2019	140521823750007982	2300002
10	APRIL-19-MANUAL-10002	APR-2019	140521823750008533	2300003

dataset name = test2

Obs	TRAN_ID	MTH	POLICYNO	Endt_No
1	APRIL-19-MANUAL COINS-4	APR-2019	1517652212051195	32009
2	APRIL-19-MANUAL COINS-5	APR-2019	1517652212051000	32009
3	APRIL-19-MANUAL-1	APR-2019	140521823120084451	2300001
4	APRIL-19-MANUAL-14	APR-2019	140521823120084452	100
5	APRIL-19-MANUAL-15	APR-2019	140521823120078000	200
6	APRIL-19-MANUAL-1000	APR-2019	140521823120078959	2300001
7	APRIL-19-MANUAL-10000	APR-2019	140521823120079000	300
9	APRIL-19-MANUAL-10001	APR-2019	140521823750007982	2300002
10	APRIL-19-MANUAL-10002	APR-2019	140521823750008533	2300003

Code -

proc sql;
update test1 A
set MTH
=(select MTH FROM test2 B WHERE A.TRAN_ID = B.TRAN_ID) where MTH=' ';
update test1 A
set POLICYNO
=(select POLICYNO FROM test2 B WHERE A.TRAN_ID = B.TRAN_ID) where POLICYNO=' ';
update test1 A
set Endt_No
=(select Endt_No FROM test2 B WHERE A.TRAN_ID = B.TRAN_ID) where Endt_No=' ';
quit;

[Kurt_Bremser]

As I already stated, I would not use SQL for this.

What do you mean by "does not work"? Post the log of your step, and equally post the log of your try to adapt my code.

Always post the complete log (all code and messages, if messages are repeated multiple times, keep one instance), and use the </> button to post it.

[sanjaymane7]

Hi, My SQL code does not work on huge data. Your code is perfect and working fine. Thank you so much for help.