topic Unexpected select error in Update statement in SAS Programming

Unexpected select error in Update statement

Babloo — Fri, 15 Mar 2024 05:57:10 GMT

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;

Re: Unexpected select error in Update statement

ballardw — Fri, 15 Mar 2024 06:26:54 GMT

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.

Re: Unexpected select error in Update statement

Babloo — Fri, 15 Mar 2024 06:35:02 GMT

@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?

Re: Unexpected select error in Update statement

ballardw — Fri, 15 Mar 2024 06:41:56 GMT

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

Re: Unexpected select error in Update statement

Kurt_Bremser — Fri, 15 Mar 2024 07:01:03 GMT

POST.

THE.

LOG.

Re: Unexpected select error in Update statement

Kurt_Bremser — Fri, 15 Mar 2024 07:14:39 GMT

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.

Re: Unexpected select error in Update statement

Babloo — Fri, 15 Mar 2024 07:33:48 GMT

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?

Re: Unexpected select error in Update statement

Kurt_Bremser — Fri, 15 Mar 2024 07:41:35 GMT

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

Re: Unexpected select error in Update statement

Mazi — Fri, 15 Mar 2024 09:34:52 GMT

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;