Repeat code on several input tables to load data in a table

[mariapf]

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 

[AMSAS]

Hi,

I'm not clear on what you are attempting to achieve, but I think you want to run the code multiple times. If that's the case then the following example might help:

/* Create a macro that contains the code I want to run multiple times */
/* Passing a parameter (runNTimes) telling the macro how many times I want to run the common code */
%macro common(runNTimes) ;
	/* Set up a macro do loop to run the common code runNTimes */
	%do i=1 %to &runNTimes ;
		/* Common code I want to run */
		/* &i is a macro variable that will resolve to the value of i */
		data example&i ;
			var_c="This was generated on run "!!putn(&i,"8.") ;
		run ;
		/* end the macro do loop */
	%end ;
	/* end of the macro definition */
%mend ;

/* Call the macro, passing the number of times I want to repeat the do loop */
%common(3) ;

[mariapf]

I am sorry, I understand that I might not been super clear. Yes, I want to run the code multiple times on multiple input tables.

Is common the name of the macro and runNTimes is a parameter that I define beforehand with the number of times I must run the macro? In my case, the number of times would be the number of input tables that I would have to run the code on. 

I understand that I can write all the code that I have in place of the example data step that you wrote, using &i for the runNTimes index that it's looping over?

[Reeza]

You need to delve into macros now. Here are some general references you may find helpful. 

UCLA introductory tutorial on macro variables and macros
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Ap...

[Kurt_Bremser]

First identify the elements of your code that will change between runs.

Replace those elements with macro variables, set the variables with %LET on top of your code, and test if it works.

Next, wrap your code into a macro definition (%MACRO COMMON starts the definition of a macro called common), and use the macro variabe names as macro parameters.

Finally, devise a way to get those paraneters automatically; often this can be done by storing them in a dataset, one observation per planned run.

Then you can use various means to automatically call the macro (CALL EXECUTE, or writing the macro calls to a file that you %INCLUDE later).

Follow these steps, and come back to us as soon as you run into a problem along the way.