Hi, I am getting an error message that the variable is not on File work.Tableranges. I modified a sas program I use thats uses the actual fiscal year dates which works just fine. I have included the code and output as well as the log with the error meesage. The modified code seems to work up to the point where the TableRanges is created. Not too sure whats happening after that point. Any suggestions would be greatly appreciated. Thanks in advance.
%INCLUDE 'U:\SAS Templates\rac.sas';
LIBNAME myproj 'C:\Dps';
DATA _NULL_;
fromdate = strip(put(intnx('year.4',today(),-3,'B'),yymmddn8.));
todate = strip(put(intnx('year.4',today(),-1,'E'),yymmddn8.));
CALL Symput ('fromdate', fromdate);
CALL Symput ('todate', todate);
RUN;
DATA Dates;
startday = 20040401;
date = input(put(startday,8.),yymmdd8.);
FORMAT date date9.;
endDay = intnx("YEAR.4", today(), -2);
DO WHILE(date <= endDay);
OUTPUT;
date = intnx("YEAR", date, 1, "SAME");
END;
DROP endDay;
FORMAT date yymmddn8.;
RUN;
startday date
20040401 20040401
20040401 20050401
20040401 20060401
20040401 20070401
20040401 20080401
20040401 20090401
20040401 20100401
20040401 20110401
20040401 20120401
PROC SQL;
CREATE TABLE Dates_Info AS
SELECT Dates.date,
(input(substr(put(Dates.date,yymmddn8.),1,6),8.)) LABEL="FirstDay" AS FirstDay,
(input(substr(put(INTNX('Year.4', Dates.date, 0,'E'),yymmddn8.),1,6),8.)) AS LastDay,
(SUBSTR(put(Dates.date,yymmddn8.),3,2)) AS Class1,
(SUBSTR(put(INTNX('Year.4', Dates.date, 0,'E'),yymmddn8.),3,2)) AS Class2,
(case when (input(put(Dates.date,yymmddn8.),yymmddn8.)) <= 20070401
then catt('myproj.sar',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0,
'E'),yymmddn8.),3,2)))
else catt('myproj.pdp',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0,
'E'),yymmddn8.),3,2)))
end) AS Class format=$32.,
(case when (input(put(Dates.date,yymmddn8.),yymmddn8.)) = 20040401
then catt('myproj.dura',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0,
'E'),yymmddn8.),3,2)))
when (input(put(Dates.date,yymmddn8.),yymmddn8.)) <= 20070401
then catt('myproj.durb',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0,
'E'),yymmddn8.),3,2)))
else catt('myproj.durc',(SUBSTR(put(Dates.date,yymmddn8.),3,2)),(SUBSTR(put(INTNX('Year.4', Dates.date, 0,
'E'),yymmddn8.),3,2)))
end) AS Class3 format=$32.
FROM WORK.DATES;
QUIT;
date FirstDay LastDay Class1 Class2 Class Class3
20040401 200404 200503 04 05 myproj.sar0405 myproj.dura0405
20050401 200504 200603 05 06 myproj.sar0506 myproj.durb0506
20060401 200604 200703 06 07 myproj.sar0607 myproj.durb0607
20070401 200704 200803 07 08 myproj.sar0708 myproj.durb0708
20080401 200804 200903 08 09 myproj.pdp0809 myproj.durc0809
20090401 200904 201003 09 10 myproj.pdp0910 myproj.durc0910
20100401 201004 201103 10 11 myproj.pdp1011 myproj.durc1011
20110401 201104 201203 11 12 myproj.pdp1112 myproj.durc1112
20120401 201204 201303 12 13 myproj.pdp1213 myproj.durc1213
PROC SQL;
CREATE TABLE Current AS
SELECT
MAX(Dates_Info.LastDay) AS MAX_of_date
FROM WORK.Dates_Info;
QUIT;
DATA Current;
SET Current;
MAX_of_date = MAX_of_date || '01';
RUN;
DATA Current;
SET Current;
FirstDay = input(strip(substr(put(intnx('Month',input(put(MAX_of_date,8.),yymmdd8.),1,'B'),yymmddn8.),1,6)),6.);
RUN;
MAX_of_date FirstDay
20130301 201304
DATA ab;
SET Current;
LastDay = input(strip(substr(put(intnx('Month',today(),-1,'E'),yymmddn8.),1,6)),6.);
Class = 'rac.Cend';
Class3 = 'rac.Nonj';
RUN;
MAX_of_date FirstDay LastDay Class Class3
20130301 201304 201501 rac.Cend rac.Nonj
DATA TableRanges (DROP=date Class1 Class2 MAX_of_date);
SET dates_info ab;
RUN;
FirstDay LastDay Class Class3
200404 200503 myproj.sar0405 myproj.dura0405
200504 200603 myproj.sar0506 myproj.durb0506
200604 200703 myproj.sar0607 myproj.durb0607
200704 200803 myproj.sar0708 myproj.durb0708
200804 200903 myproj.pdp0809 myproj.durc0809
200904 201003 myproj.pdp0910 myproj.durc0910
201004 201103 myproj.pdp1011 myproj.durc1011
201104 201203 myproj.pdp1112 myproj.durc1112
201204 201303 myproj.pdp1213 myproj.durc1213
201304 201501 rac.Cend rac.Nonj
PROC SQL NOPRINT;
SELECT Class
INTO :tableList separated by ' '
FROM TableRanges
WHERE input(substr(put(LastDay,8.),1,6),6.) >= input(substr(put(&fromDate,8.),1,6),6.) and input(substr(put(FirstDay,8.),1,6),6.) <= input(substr(put(&toDate,8.),1,6),6.);
QUIT;
PROC SQL NOPRINT;
SELECT catx(' ','date_processed_ym between', input(substr(put(&fromDate,8.),1,6),6.),'and',input(substr(put(&toDate,8.),1,6),6.))
INTO :wherelist separated by ' OR '
FROM TableRanges
WHERE input(substr(put(LastDay,8.),1,6),6.) >= input(substr(put(&fromDate,8.),1,6),6.) and input(substr(put(FirstDay,8.),1,6),6.) <= input(substr(put(&toDate,8.),1,6),6.);
QUIT;
%LET wherelist = &wherelist;
DATA Summary4;
SET &tableList;
WHERE (("&wherelist")
AND (date_processed_ym between (input(substr(&fromDate,1.6),6.)) and (input(substr(&toDate,1,6),6.)))
AND (sid=1));
DROP ref_num rec_id;
RUN;
LOG With Error Message:
109
110 %LET wherelist = &wherelist;
111
112 DATA Summary4;
113 SET &tableList;
114 WHERE (("&wherelist")
115 AND (date_processed_ym between (input(substr(&fromDate,1.6),6.)) and
115 ! (input(substr(&toDate,1,6),6.)))
116 AND (sid=1));
ERROR: Variable date_processed_ym is not on file WORK.TABLERANGES.
117 DROP ref_num rec_id;
118 RUN;
