Update table in proc sql

[Babloo]

I want to update the ir.INSURANCE_CF dataset which should have the value for the variable INSURANCE_CONTRACT_GROUP_ID per the below calculation. With the below code I could only recreate the ir.INSURANCE_CF dataset  whereas I was asked to only update the dataset.

Is there a way that we can tweak the code below to update the original dataset instead of recreating the same?

[Jagadishkatam]

Could you please try the below code, we do not need to name the new variable as INSURANCE_CONTRACT_GROUP_ID_NEW, instead use the same existing name INSURANCE_CONTRACT_GROUP_ID

proc sql;
create table ir.INSURANCE_CF as
select
  a.*,
  b.INITIAL_RECOGNITION_DT,
  compress(cats(a.INSURANCE_CONTRACT_GROUP_ID,put(a.ISSUE_DT,date9.),put(b.INITIAL_RECOGNITION_DT,date9.)),'.')
  as INSURANCE_CONTRACT_GROUP_ID length=72
from IFRSLDIS.INSURANCE_CASHFLOW  as a
left join IFRSLDIS.ADP_OUTPUT_NON_LIFE as b
on
  a.REPORTING_DT = b.REPORTING_DT and
  a.ENTITY_ID = b.UNIT and
cats(substr(put(ISSUE_DT,yymmn6.),1,4),"-",substr(put(ISSUE_DT,yymmn6.),5)) = b.ISSUE_MONTH
;
quit;

[Babloo]

It is recreating the table, right? I just want to update the permanent
table with updated field values for that variable based on the below
calculation

[Kurt_Bremser]

Since you change the structure, you can't update, only rewrite.

[Babloo]

I'm not changing the structure of the table. I just want to populate the
updated values in one variable as I mentioned in my OP

[Kurt_Bremser]

I don't see an opportunity for an update anyway, as you use

IFRSLDIS.INSURANCE_CASHFLOW
IFRSLDIS.ADP_OUTPUT_NON_LIFE

as input to create a completely new dataset. You do not read

ir.INSURANCE_CF

anywhere in your code.

[Babloo]

There is a typo.

ir. should be read as IFRSLDIS.

I want to update IFRSLDIS.Insurance_Cashflow table.

[Kurt_Bremser]

See this quick example for updating an existing column with data from another table:

data class;
set sashelp.class;
run;

data class1;
set sashelp.class (keep=Name);
x1 = _N_;
run;

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

[Kurt_Bremser]

BTW, why do you still use

cats(substr(put(ISSUE_DT,yymmn6.),1,4),"-",substr(put(ISSUE_DT,yymmn6.),5))

when

put(ISSUE_DT,yymmd7.)

achieves the same (as I showed in your previous thread)?

[Babloo]

I got it. Could you please tell me how will you optimize the code below to update the permanent table?

I'm trying to do optimize the above second step as follows, but I couldn't succeed as I'm not certain how to set the values in INSURANCE_CONTRACT_GROUP_ID

I've 16 columns in IFRSLDIS.INSURANCE_CASHFLOW and therefore I should have same columns in the final result with updated values for INSURANCE_CONTRACT_GROUP_ID

[Patrick]

@Babloo 

What does the SAS log tell you when you execute the SQL as posted?

1. A SQL update can only change values in existing cells. It can NOT add columns or rows. So: Any DROP statement is already wrong.

2. You update CELLS and for this reason the sub-select must return exactly one value. The sub-select must also be valid SQL code which yours isn't.

select * from temp compress(cats(INSURANCE_CONTRACT_GROUP_ID,put(ISSUE_DT,date9.),put(INITIAL_RECOGNITION_DT,date9.)),.

Please try and check that again - like all cats() stuff needs to go into the select clause and you also need to add a where condition to only select the row which matches the record in the table you want to update. May be try first to fully understand the sample code @Kurt_Bremser posted and also read up how a SQL Update works in documentation.

https://go.documentation.sas.com/?docsetId=sqlproc&docsetTarget=p0z9p6hclwnhxin1mrewxhdz5f26.htm&doc... 

[Kurt_Bremser]

I won't waste a second of my time trying to read this ugly piece of spaghetti text.

Please supply CODE.

See my many posts (and those of the other superusers and PROC STARS) for how to write code in a readable manner.