topic Update table in proc sql in SAS Programming

Update table in proc sql

Babloo — Wed, 28 Aug 2019 11:37:44 GMT

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?

Re: Update table in proc sql

Jagadishkatam — Wed, 28 Aug 2019 08:08:21 GMT

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;

Re: Update table in proc sql

Babloo — Wed, 28 Aug 2019 08:17:35 GMT

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

Re: Update table in proc sql

Kurt_Bremser — Wed, 28 Aug 2019 08:21:20 GMT

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

Re: Update table in proc sql

Babloo — Wed, 28 Aug 2019 08:23:35 GMT

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

Re: Update table in proc sql

Kurt_Bremser — Wed, 28 Aug 2019 08:32:25 GMT

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.

Re: Update table in proc sql

Kurt_Bremser — Wed, 28 Aug 2019 08:41:25 GMT

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

Re: Update table in proc sql

Babloo — Wed, 28 Aug 2019 08:45:35 GMT

There is a typo.

ir. should be read as IFRSLDIS.

I want to update IFRSLDIS.Insurance_Cashflow table.

Re: Update table in proc sql

Kurt_Bremser — Wed, 28 Aug 2019 08:55:10 GMT

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;

Re: Update table in proc sql

Babloo — Wed, 28 Aug 2019 11:38:50 GMT

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

Re: Update table in proc sql

Patrick — Wed, 28 Aug 2019 10:39:16 GMT

@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&docsetVersion=9.4&locale=en

Re: Update table in proc sql

Kurt_Bremser — Wed, 28 Aug 2019 10:34:08 GMT

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.