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;
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.
@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?
@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.
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.
Either provide the log, or usable example data and the expected result.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.