re: Error Message Variable not on File

twildone — Fri, 27 Feb 2015 19:23:13 GMT

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;

Re: re: Error Message Variable not on File

sbb — Fri, 27 Feb 2015 20:07:06 GMT

From your SAS code piece, file WORK.TABLERANGES is sourced from a DATA step that inputs with a SET dates_info ab; specified. Neither of those two files has a SAS variable with the name you are getting for the SAS ERROR.

topic Re: re: Error Message Variable not on File in SAS Programming

re: Error Message Variable not on File

Re: re: Error Message Variable not on File