I would appreciate any suggestions on the proc sql below. I have shortened the query but basically I have 4 instances of case statements in this query where the between date ranges are repeated and I would like to automate the dates in a way where they constantly change within a set parameter and save different output files separately. For example if the d.origi_dt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31' then 1. copy these dates on all 4 case statements without manually changing them every time 2. but also change it to the next date range interval which is between '2012-08-01 and date '2012-08-05' and so on and so on. I know there is 'INTNX' variable but I am not too familiar on its application. If any SAS expert could lead me into the right direction or show me any code examples that i can replicate for my query on how to automate this script that would be great help as I have hit a roadblock here.
options mlogic symbolgen;
%let sasEngine = /user/local/sas;
libname here '/test/tmap/secmov';
/* pull data*/ Proc SQL;
connect to oracle (database=);
create table here.abc as
select * from connection to oracle
(SELECT c.rgi_nb, c.adr.nb, c.occ_nb, c.sty_dt, a.dpt_dt '-' as Duration,
(Case When SUBSTRING (a.rgi_nb FROM 1 FOR 2) IN ('H', 'C') Then 'Other'
When SUBSTRING (a. FROM 1 FOR 2) IN ('CN') Then 'Brand1'
When SUBSTRING (a.Txt FROM 1 FOR 1) IN ('C','X') Then 'Brand2'
Else 'Other' END) as BB1,
(Case When SUBSTRING (a.base_Txt FROM 2 FOR 2) IN ('NZ') Then 'FO'
Else 'Other' END) as GO,
WHERE (d.origi_Dt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31')
WHERE ((d.origi_LDt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31'
AND WHERE ((d.origi_LDt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31'
AND WHERE ((d.origi_LDt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31'
AND quit;
... View more
