Error: Function MID could not be located

[izzytetteh24]

TABULAR_MORTALITY,
5821 FY_PREMIUM_LOADS,
5822 REN_PREMIUM_LOADS,
5823 PARTIAL_SURRENDERS,
5824 NET_SURRENDERS,
5825 SURRENDER_CHARGES,
5826 MATURITIES,
5827 DEATHS,
5828 BALANCE_ENDING,
5829 MID(ADMIN_PLAN_CODE,6,1) AS Expr1
5830 FROM WORK.BLCCRF
5831 WHERE ISSUE_DATE > 20181231 AND ISSUE_DATE < 20200101 AND SOX_CYCLE = "93" AND Expr1 <> "M" AND Expr1 <> "N"
5832 ORDER BY POLICY_NUMBER
5833 ;
NOTE: The "<>" operator is interpreted as "not equals".
ERROR: Function MID could not be located.
ERROR: The following columns were not found in the contributing tables: Expr1.

5835 Proc SQL;

5836 CREATE TABLE Work.BCLICNBTotals AS
5837 SELECT
5838 Sum(POLICY_COUNT) AS POLICY_COUNT,
5839 Sum(BALANCE_BEGINNING) AS BALANCE_BEGINNING,
5840 Sum(PREMIUM) AS PREMIUM,
5841 Sum(BASE_INTEREST) AS BASE_INTEREST,
5842 Sum(EXCESS_INTEREST) AS EXCESS_INTEREST,
5843 Sum(TOT_ADDITIONS) AS TOT_ADDITIONS,
5844 Sum(PARTIAL_SURRENDERS) AS PARTIAL_SURRENDERS,
5845 Sum(NET_SURRENDERS) AS NET_SURRENDERS,
5846 Sum(SURRENDER_CHARGES) AS SURRENDER_CHARGES,
5847 Sum(MATURITIES) AS MATURITIES,
5848 Sum(DEATHS) AS DEATHS,
5849 Sum(TOT_DEDUCTIONS) AS TOT_DEDUCTIONS,
5850 Sum(BALANCE_ENDING) AS BALANCE_ENDING,
5851 Sum(CASH_SURRENDER_VALUE) AS CASH_SURRENDER_VALUE
5852
5853 FROM Work.BLCCRF
5854
5855 WHERE SYSTEM_COMPANY = "48" AND ISSUE_DATE > 20180930
5856 ;

I have really two issues or two programs that need debugging. The first at the top isn't recognizing MID as a function which I'm not sure why. Also, the ADMIN_PLAN_CODE field that's being called is of type character if that matters. The second is a little more confusing. It runs just fine but returns no records even though I know from the Work.BLCCRF table there should be about 233 policies that are summed with ISSUE_DATE > 20180930 and SYSTEM_COMPANY = "48". 

[Shmuel · Accepted solution]

1)  MID is not a sas function (origin probably from execl). Replace it with SUBSTR. The syntax is the same   substr(<string>,<from position>,<length>);

If length is omitted it will extract substring up to the end of the string.

2) assuming ISSUE_DATE is a sas numeric date you need to use a specific format of date literaL:  20180930 should be '30sep2018'd.

The literal of a time stamp (date and time) is '30sep2018:00:00:00'dt;

A sas date is counting days since 01jun1960 which holds the value 0.

(excel holds number of days since 01jan1900).

[ballardw]

---

@izzytetteh24 wrote:

The second is a little more confusing. It runs just fine but returns no records even though I know from the Work.BLCCRF table there should be about 233 policies that are summed with ISSUE_DATE > 20180930 and SYSTEM_COMPANY = "48". 

---

Best would be run proc contents on the data set and show us the properties for the variables Issue_date and System_company.

I suspect that your Issue_date may be an actual SAS date value, which would be numeric and a format, possibly like YYMMDDn8. assigned. In which case the simple integer of 20180930 would be the wrong value to compare. If a variable is an actual SAS date value then you provide literal dates in the form of "ddMONYYYY"D, the quotes can be single or double and the year could be 4 or 2 digits but must look like "15JAN2021"d or similar. The quotes followed immediately by a D tell SAS the value is to be treated as a date. And the DDMonYY is important because if you use a value like 010203 it is not possible to tell which bit is day, month or year. So to make programmers jobs easier there is only one way to create date literal values so you can recognize them easily.