Unexpected select error in Update statement

[Babloo]

Need help to tackle this update statement? It's not working now due to unexcepted Select statement error in the SET statement.

UPDATE XF.POS_PROC_COD_TEST PCI
SET PCI.PROC_CD_DESC  = 
FROM (
	SELECT 
		PDC.id,
		max(
			CASE WHEN ( COALESCE(SERVICE_FROM_DT, TO_DATE('20990101', 'YYYYMMDD')) ) BETWEEN EFFECTIVE_DATE AND 
			 (CASE WHEN EXPIRATION_DATE IS NULL THEN '2099-01-01' ELSE EXPIRATION_DATE END)
			 THEN DESCRIPTION END) PROC_CD_DESC_NEW
	FROM XF.POS_PROC_COD_TEST PDC
	LEFT JOIN XF.POS_CLAIM_HEDR TPCC ON PDC.O_CLAIM_ID = TPCC.O_CLAIM_ID                   
	LEFT JOIN (
		SELECT DISTINCT ICD_PROC_CODE, EXPIRATION_DATE,EFFECTIVE_DATE, DESCRIPTION  FROM
		COM_DB.COM_DATA.MR_ICD_PROC) CDMRIP
		ON CDMRIP.ICD_PROC_CODE = PDC.PROC_CD
	GROUP BY PDC.id
) Q1
WHERE PCI.id = Q1.id and PCI.id = 0411;

[ballardw]

How about showing the LOG with the code and the actual text of the error message and diagnostic characters plus any other notes or warnings.

And if this is connecting to an external data base (i.e. not using SAS data sets) perhaps include the connection options.

[Babloo]

@ballardw Statement is not valid error. I want to know what is wrong with the SET statement.

@Kurt_Bremser  any help you can offer here?

[ballardw]

---

@Babloo wrote:

@ballardw Statement is not valid error.

---

What does "not valid error" mean?   Just because you don't know why it is an error does not mean it is not an error.

We don't have any of your so can't run the code.

The use of the "dates" that you show pretty much implies you are working with something other basic SAS data sets, so the question may well be in that system and not SAS.

[Kurt_Bremser]

POST.

THE.

LOG.

[Kurt_Bremser]

Something like

TO_DATE('20990101', 'YYYYMMDD')

can only be valid in some other, non-SAS SQL dialect, so it would have to be part of explicit pass-through.

Once again, post the complete log.

[Babloo]

I don't have log at the moment. Is it right the way to use case when with
select in the proc sql update statement?

[Kurt_Bremser]

Either provide the log, or usable example data and the expected result.

[Mazi]

I don't think your syntax is correct.

if you're using proc sql without passthru facility the syntax would look something like this:

data test;
	set sashelp.class;
	age=.; **Set age to missing and update using sql query;
run;

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