Teradata Macro Variables

Tools & Utilities

Teradata Macro Variables

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;
Tags (2)