topic Re: Update statement based on Join condition not working in SAS Programming

Update statement based on Join condition not working

Babloo — Wed, 13 Mar 2024 07:08:49 GMT

Any help to resolve this issue? Getting an error like unexpected 'on' at line number 4

update pre_claim_lin_1_test pci
set pci.LINE_DIA_PRIM_DESC = diag.description 
from (select max(description) description from COMDB.ON_DATA.MR_DIAGNOSIS_CODE c inner join raw.prepa_claim_inst r on r.PRINCIPAL_DIAG = c.DIAGNOSI_CODE
where  c.ICD1_INDICATOR<>'N')diag on 1=1 and pci.id  =7828 ;

If I tried to remove ON and add WHERE, I'm getting an error like invalid field diag.description

update pre_claim_lin_1_test pci
set pci.LINE_DIA_PRIM_DESC = diag.description 
from (select max(description) description from COMDB.ON_DATA.MR_DIAGNOSIS_CODE c inner join raw.prepa_claim_inst r on r.PRINCIPAL_DIAG = c.DIAGNOSI_CODE
where  c.ICD1_INDICATOR<>'N')diag where pci.id  =7828 ;

Re: Update statement based on Join condition not working

Mazi — Wed, 13 Mar 2024 07:41:37 GMT

data test;
	set sashelp.class;
	age=.;
run;

proc sql;
	update test a
		set age = (select age from sashelp.class as b where a.name = b.name);
quit;

Can you try something similar to this?

Create a temporary table first where you select the max description and use that table in the set statement.

If you have some sample data, please share it, and I can show you a practical example with your data.

Re: Update statement based on Join condition not working

Kurt_Bremser — Wed, 13 Mar 2024 07:43:54 GMT

Please post the complete log, so we can see the position of the error indicator.

Re: Update statement based on Join condition not working

Kurt_Bremser — Wed, 13 Mar 2024 07:48:18 GMT

Replace the <> with the NE mnemonic. In SAS, <> is the MAX operator.