Below is a SAS script that passes variables thru the same code many times. I would like to do the same type of thing in Teradata with my initial code looking like this for an UPDATE.
1. First, this is an UPDATE statement 2. I need to observe the current month's and prior month's MISS_PMT_COUNT over years to determine the MOST RECENT TIME when a mortgage account went from delinquent to cured and vise versa. 3. If there is some way to determine &N observations on the MIN(STATEMENT_DATE) for the pass through values, that would be great.
Thank you in advance,
CREATE MACROUD460.DELQ_DT_ENTER_CURED (INCR SMALLINT) AS ( SEL * FROM
UPDATE A FROM UD460.MUDS_PERFORMANCE_DEV A, ( SELECT ACCOUNT_NUMBER, MAX(STATEMENT_DATE) AS MAX_ST_DT, ADD_MONTHS(MAX(STATEMENT_DATE),-1) AS PRIOR_MONTH FROM UD460.MUDS_PERFORMANCE_DEV GROUP BY 1 ) Z
SET Date_Delinquency_Cured = CASE WHEN A.STATEMENT_DATE = (Z.MAX_ST_DT - INCRE) AND A.MISS_PMT_COUNT = 0 THEN CASE WHEN A.STATEMENT_DATE = (Z.PRIOR_MONTH – INCR) AND A.MISS_PMT_COUNT > 0 THEN (Z.MAX_ST_DT – INCR) END END,
Date_Entered_Delinquency = CASE WHEN A.STATEMENT_DATE = (Z.MAX_ST_DT - INCRE) AND A.MISS_PMT_COUNT < 0 THEN CASE WHEN A.STATEMENT_DATE = (Z.PRIOR_MONTH – INCR) AND A.MISS_PMT_COUNT = 0 THEN (Z.MAX_ST_DT – INCR) END END,
WHERE A.ACCOUNT_NUMBER = Z.ACCOUNT_NUMBER AND A.MORE_SOURCE_NAME= 'BANK SOUTH' ;
/* SAS CODE EXAMPLE */ proc sql noprint; select variable into :VarList separated by ' ' from TEST /* MYLIB.VARIABLES */; quit; %let n=&sqlObs; %put &N;
/* WILL USE MACRO TO CREATE HISTOGRAM OUTPUT */ %macro histogram; %do i=1 %to &N.; %let Var=%scan(&varlist,&i); ODS HTML FILE= "C:\Documents and Settings\qpw144\My Documents\Work\MUDS\Release 1\images\html\&var..html" gpath= 'C:\Documents and Settings\qpw144\My Documents\Work\MUDS\Release 1\images\html\';
title "BANK SOUTH &var. Histogram"; proc univariate data=ud461.muds_loans_r_1 (where=(more_source_name = 'BANK SOUTH')) noprint; histogram &var. / NORMAL (COLOR = red) CFILL = ltgray CTEXT = blue; run;
ODS HTML CLOSE; %end; %mend histogram; %HISTOGRAM;