topic Re: Left Join/ Update Problem in New SAS User

Left Join/ Update Problem

sanjaymane7 — Mon, 19 Apr 2021 10:10:18 GMT

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;

Re: Left Join/ Update Problem

PaigeMiller — Mon, 19 Apr 2021 10:50:42 GMT

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).

Re: Left Join/ Update Problem

sanjaymane7 — Tue, 20 Apr 2021 05:44:32 GMT

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

Re: Left Join/ Update Problem

PaigeMiller — Tue, 20 Apr 2021 10:39:29 GMT

Show us the code you are using now.

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

Re: Left Join/ Update Problem

sanjaymane7 — Wed, 21 Apr 2021 06:38:28 GMT

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;

Re: Left Join/ Update Problem

Kurt_Bremser — Wed, 21 Apr 2021 07:27:12 GMT

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.

Re: Left Join/ Update Problem

sanjaymane7 — Thu, 22 Apr 2021 17:14:51 GMT

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

Re: Left Join/ Update Problem

Kurt_Bremser — Thu, 22 Apr 2021 17:32:22 GMT

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.

Re: Left Join/ Update Problem

sanjaymane7 — Fri, 07 May 2021 11:48:12 GMT

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;

Re: Left Join/ Update Problem

Kurt_Bremser — Fri, 07 May 2021 17:14:31 GMT

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.

Re: Left Join/ Update Problem

sanjaymane7 — Sat, 08 May 2021 05:30:34 GMT

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