Hi all, I have developed a code that takes data from one (or several, but usually there is just one that changes), makes computations and transformations to the data and loads it in an empty table. These transformations take several data and proc sql steps. I have just found out that I will be receiving several input tables for which I need to make the same computations and transformations to the data and then load into the empty table, so with each new input I will be adding more rows to that predetermined empty table. The end result table after loading transformed data from all input tables would then look like this (I put DATA1 and DATA2 to show that it's data from different input tables): DATE CASHFLOW GROUP_NAME 01/02 65789 DATA1 01/02 3445 DATA1 01/02 3456 DATA2 01/02 775 DATA2 I must fill several tables (the predetermined empty ones) using this same method. I have the code to proceed with all the transformations and computations for all of them but I am trying to find an efficient way to take all the input tables, execute the code in each one and load into the empty tables. Is there a way to do this through a loop or a macro? I am not very experienced with SAS and this is very difficult to figure out for me. I have attached example code of all the transformations and computations I need to do in one of the tables that I must fill out. This is one of the simpler ones... %LET TABLA = INSURANCE_CONTRACT_GROUP;
PROC SQL;
CREATE TABLE WORK.STEP1 AS
SELECT
insurance_contract_group,
SCAN(insurance_contract_group,1,'#') AS INSURANCE_CONTRACT_PRTFL_ID,
CASE
WHEN insurance_contract_group CONTAINS 'NOON' THEN 'NOT ONEROUS'
ELSE 'ONEROUS'
END AS REGLTRY_GROUP_CLASS_CD
FROM WORK.STEP0;
QUIT;
PROC SQL;
CREATE TABLE WORK.STEP2 AS
SELECT
(SELECT VALOR FROM WORK.BIM05 WHERE CAMPO = 'ENTITY_ID') AS ENTITY_ID,
(SELECT MAX(date) FROM WORK.EJEMPLO) AS REPORTING_DT, /*cambiar -> RAFM*/
t1.insurance_contract_group AS INSURANCE_CONTRACT_GROUP_ID,
'REGULAR' AS INSURANCE_CONTRACT_GROUP_CD,
t1.INSURANCE_CONTRACT_PRTFL_ID,
'sasdemo' AS WORKGROUP,
t2.PRODUCT_LINE_ID,
t1.REGLTRY_GROUP_CLASS_CD,
(SELECT VALOR FROM WORK.BIM05 WHERE CAMPO = 'COUNTRY_CD') AS COUNTRY_CD,
0 AS NO_CONTRACTS, /*CUIDADO*/
'' AS TRANSITION_APPROACH_CD,
'' AS TRANSITION_REPORTING_APPROACH_CD,
'' AS TRANSITION_FLG,
0 AS TRANSITION_FV_UNDERLYING_IT_AMT,
0 AS TRANSITION_CHR_POLICYHOLDER_AMT,
0 AS TRANSITION_PAID_AMT,
0 AS TRANSITION_FAIR_VALUE_AMT,
0 AS TRANSITION_REL_RA_PAST_PER_AMT,
0 AS TRANSITION_CUMULATIVE_OCI_AMT,
0 AS TRANSITION_CSM_PAST_UNWIND_AMT,
0 AS TRANSITION_INPUT_CSM_AMT
FROM WORK.STEP1 t1 LEFT JOIN WORK.BIM02 t2 ON t1.INSURANCE_CONTRACT_PRTFL_ID = t2.PORTFOLIO;
QUIT;
PROC SQL;
ALTER TABLE WORK.STEP2
MODIFY ENTITY_ID VARCHAR(36)
MODIFY REPORTING_DT DATE FORMAT=&DTFMT INFORMAT=&DTFMT
MODIFY INSURANCE_CONTRACT_GROUP_ID VARCHAR(36)
MODIFY INSURANCE_CONTRACT_GROUP_CD VARCHAR(10)
MODIFY INSURANCE_CONTRACT_PRTFL_ID VARCHAR(36)
MODIFY WORKGROUP VARCHAR(32)
MODIFY PRODUCT_LINE_ID VARCHAR(36)
MODIFY REGLTRY_GROUP_CLASS_CD VARCHAR(20)
MODIFY COUNTRY_CD VARCHAR(3)
MODIFY NO_CONTRACTS NUMERIC(18,5) FORMAT=NLNUM18.5 INFORMAT=NLNUM18.5
MODIFY TRANSITION_APPROACH_CD VARCHAR(10)
MODIFY TRANSITION_REPORTING_APPROACH_CD VARCHAR(10)
MODIFY TRANSITION_FLG VARCHAR(1)
MODIFY TRANSITION_FV_UNDERLYING_IT_AMT NUMERIC(8)
MODIFY TRANSITION_CHR_POLICYHOLDER_AMT NUMERIC(8)
MODIFY TRANSITION_PAID_AMT NUMERIC(8)
MODIFY TRANSITION_FAIR_VALUE_AMT NUMERIC(8)
MODIFY TRANSITION_REL_RA_PAST_PER_AMT NUMERIC(8)
MODIFY TRANSITION_CUMULATIVE_OCI_AMT NUMERIC(8)
MODIFY TRANSITION_CSM_PAST_UNWIND_AMT NUMERIC(8)
MODIFY TRANSITION_INPUT_CSM_AMT NUMERIC(8);
QUIT;
PROC SQL;
INSERT INTO &LIBRERIA..&TABLA.
SELECT
*
FROM
WORK.STEP2;
QUIT;
PROC SQL;
DROP TABLE WORK.STEP0, WORK.STEP1, WORK.STEP2;
QUIT; Any help is appreciated, thank you so much for your time
... View more