ERROR: The following columns were not found in the contributing tables: null.

[req41273]

Why am I getting this error 

ERROR: The following columns were not found in the contributing tables: null.

Please see code

1                                                          The SAS System                                14:47 Monday, June 17, 2019

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTNAME='';
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS%20EG%209.4.1/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;


24         proc sql noprint /*INOBS=100*/;
25         create table NED_Members as
26         
27         SELECT DISTINCT
28           MM.SEQ_MEMB_ID as MemberID
29         , MM.LAST_NAME as MemberLastName
30         , MM.FIRST_NAME as MemberFirstName
31         , MM.DATE_OF_BIRTH as MemberDOB
32         , MM.GENDER as M_F
33         , MM.MEDICAID_NO as MedicaidID
34         , MM.ETHNICITY_CODE
35         , MM.USER_DATE_7
36         , meh.line_of_business as LOB
37         , meh.SEQ_PROV_ID
38         , meh.IPA_ID
39         , MA.ADDRESS_LINE_1 AS ADDRESS1
40         , MA.ADDRESS_LINE_2 AS ADDRESS2
41         , MA.CITY AS CITY
42         , MA.STATE AS STATE
43         , MA.ZIP_CODE AS ZIP
44         , MA.COUNTY AS COUNTY
45         , MA.HOME_PHONE_NUMBER AS HOMEPHONE
46         , MEH.EFFECTIVE_DATE
47         , MEH.TERM_DATE
48         , MEH.PLAN_CODE
49         , PM.PROVIDER_ID
50         ,PM.LAST_NAME AS PROVIDER_LAST_NAME
51         ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
52         ,PM.PROVIDER_TYPE
53         ,PM.LICENSE
54         ,PA.COUNTY AS PROV_COUNTY
55         ,PA.ADDR_CATEGORY_CODE
2                                                          The SAS System                                14:47 Monday, June 17, 2019

56         ,MI.IDNUMBER AS SUBSCRIBER_ID
57         ,MP.ATTRRECNO AS ATTRRECNO
58         ,MP.ATTRRECNO AS ATTRRECNO1
59         ,MP.RECSTAT AS RECSTAT
60         ,MP.MEMRECNO AS MEMRECNO
61         , CASE
62             WHEN (MP.ATTRRECNO = 381) THEN MP.PHNUMBER
63             ELSE null
64         END AS NEHII_HOME_PHONE,
65         CASE
66             WHEN (MP.ATTRRECNO = 391) THEN MP.PHNUMBER
67             ELSE null
68         END AS NEHII_MOBILE_PHNUMBER
69         ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER
70         ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1
71         ,MAD.CITY AS NEHII_CITY
72         ,MAD.STATE AS NEHII_STATE
73         ,MAD.COUNTY AS NEHII_COUNTY
74         ,MAD.ZIPCODE AS NEHII_ZIPCODE
75         
76         
77         FROM
78         HSDREPT.MEMBER_ADDRESS as MA
79         inner join HSDREPT.MEMBER_MASTER as MM
80             on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
81         inner join HSDREPT.member_elig_history as meh
82             on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
83         inner join HSDREPT.PROV_MASTER as PM
84             on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
85         inner join HSDREPT.PROV_ADDRESS as PA
86             on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
87         LEFT JOIN MDM.MPI_MEMIDENTWC AS MI
88             on MM.MEDICAID_NO = MI.IDNUMBER
89         inner join MDM.MPI_MEMADDRWC as MAD
90             on MAD.MEMRECNO = MI.MEMRECNO
91         inner join MDM.MPI_MEMPHONEWC as MP
92             on MAD.MEMRECNO = MP.MEMRECNO
93         
94         WHERE
95         MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
96         AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
97         AND MEH.LINE_OF_BUSINESS = 'NED'
98         AND MEH.TERM_DATE IS NULL
99         AND MA.ADDRESS_TYPE = 'R5'
100        AND MA.TERM_DATE IS NULL
101        AND MAD.MEMRECNO = MI.MEMRECNO
102        AND MI.MEMRECNO = MP.MEMRECNO AND MP.ATTRRECNO IN (389,391)
103        AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390;
ERROR: The following columns were not found in the contributing tables: null.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
104        
105        quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds
      
106        run;
3                                                          The SAS System                                14:47 Monday, June 17, 2019

107        


108        proc sort data = NED_Members NODUPKEY;
ERROR: File WORK.NED_MEMBERS.DATA does not exist.
109        by MemberID;
110        
111        GOPTIONS NOACCESSIBLE;
112        %LET _CLIENTTASKLABEL=;
113        %LET _CLIENTPROCESSFLOWNAME=;
114        %LET _CLIENTPROJECTPATH=;
115        %LET _CLIENTPROJECTNAME=;
116        %LET _SASPROGRAMFILE=;
117        
118        ;*';*";*/;quit;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
118      !                run;
119        ODS _ALL_ CLOSE;
120        
121        
122        QUIT; RUN;
123        

[tsap]

I believe the problem lies within these two lines of logic:

61         , CASE
62             WHEN (MP.ATTRRECNO = 381) THEN MP.PHNUMBER
63             ELSE null
64         END AS NEHII_HOME_PHONE,
65         CASE
66             WHEN (MP.ATTRRECNO = 391) THEN MP.PHNUMBER
67             ELSE null
68         END AS NEHII_MOBILE_PHNUMBER

The end of each states else null, but there is no column named 'null' so it throws an error. If you think of it this way, you could have put else mp.PHNumber and it would populate with the values in that column.

So depending on whether or not two new phone number fields (NEHII_HOME_PHONE and NEHII_MOBILE_PHNUMBER) are numeric or character, it would change what solution you could implement.

I believe the easiest solution is to remove the 'ELSE NULL' line from each case when statement. The lack of the ELSE logic will cause anything that didn't meet the prior criteria to default to a null value.

An alternative solution would be to replace the 'NULL' that is written with value equivalent:

If the field being produced is character, then you would use "  ELSE ' '  " with a blank space between the single quotes

If the field being produced is numeric, then you would use "  ELSE '.'  " with a period between the single quotes.

Which ever solution you go with, it should resolve the issue that you are currently encountering.

Hope that helps.

[tsap · Accepted solution]

Again I state that the problem is in the CASE WHEN logic within the PROC SQL statement.

As it stands the logic says "CASE WHEN.....ELSE NULL". When it gets to the ELSE criteria it looks for a variable named 'Null'. It does not populated a null value.

Using the test example from one of the other responders. If you tried running this logic:

proc sql;
   create table test as
   select *
         ,case(sex)
          when('M') then 'Male'
          when('F') then 'Female'
          else null
          end as sex2
   from sashelp.class;
quit;

You would end up getting the same error:

7468 proc sql;

7469 create table test as

7470 select *

7471 ,case(sex)

7472 when('M') then 'Male'

7473 when('F') then 'Female'

7474 else null

7475 end as sex2

7476 from sashelp.class;

ERROR: The following columns were not found in the contributing tables: null.

So in order to run this logic without getting this error, you will need to update these two "Case When" statements where they state 'ELSE Null':

, CASE WHEN (MP.ATTRRECNO = 381) THEN MP.PHNUMBER ELSE null END AS NEHII_HOME_PHONE
, CASE WHEN (MP.ATTRRECNO = 391) THEN MP.PHNUMBER ELSE null END AS NEHII_MOBILE_PHNUMBER

Depending on whether the resulting created variable (NEHII_HOME_PHONE/NEHII_MOBILE_PHNUMBER) is formatted as numeric or character would impact what the logic update ends up being.

My assumption would be that these fields would be character, if so, the updated "Case When" statements should look like this:

, CASE WHEN (MP.ATTRRECNO = 381) THEN MP.PHNUMBER ELSE ' ' END AS NEHII_HOME_PHONE
, CASE WHEN (MP.ATTRRECNO = 391) THEN MP.PHNUMBER ELSE ' ' END AS NEHII_MOBILE_PHNUMBER

If these two fields would actually end up being formatted as numeric, then this would be the update logic:

, CASE WHEN (MP.ATTRRECNO = 381) THEN MP.PHNUMBER ELSE . END AS NEHII_HOME_PHONE
, CASE WHEN (MP.ATTRRECNO = 391) THEN MP.PHNUMBER ELSE . END AS NEHII_MOBILE_PHNUMBER

Hope this helps.

[Reeza]

IS NULL isn't valid SAS syntax.

You can use MISSING() instead

And missing(MEH.TERM_DATE)

[Patrick]

---

@Reeza wrote:
IS NULL isn't valid SAS syntax.

You can use MISSING() instead

And missing(MEH.TERM_DATE)

---

@Reeza 

Even though SAS doesn't have a concept of NULL you still can use IS NULL in a where clause - which SAS then just treats the same as IS MISSING

And what I just found out when running a test: SAS actually translates IS MISSING to IS NULL.

29         proc sql feedback;
30           create table test as
31             select *
32               ,case(sex)
33               when('M') then 'Male'
34               when('F') then 'Female'
35               else ' '
36               end as sex2
37             from sashelp.class
38             where name is not missing
39           ;
NOTE: Statement transforms to:

        select CLASS.Name, CLASS.Sex, CLASS.Age, CLASS.Height, CLASS.Weight, case CLASS.Sex
                                                                               when 'M' then 'Male'
                                                                               when 'F' then 'Female'
                                                                               else ''
                                                                               end as sex2
          from SASHELP.CLASS
         where CLASS.Name is not null;

NOTE: Table WORK.TEST created, with 19 rows and 6 columns.

[ScottBass]

Like @Reeza said but with code examples.  PROC SQL has slight differences to ANSI SQL - NULL vs. MISSING is one of them.

proc sql;
   create table test as
   select *
         ,case(sex)
          when('M') then 'Male'
          when('F') then 'Female'
          else null
          end as sex2
   from sashelp.class;
quit;

proc sql;
   create table test as
   select *
         ,case(sex)
          when('M') then 'Male'
          when('F') then 'Female'
          else ''
          end as sex2
   from sashelp.class;
quit;

proc sql;
   create table test as
   select *
         ,case(sex)
          when('M') then 1
          when('F') then 2
          else .
          end as sex2
   from sashelp.class;
quit;

[req41273]

Thanks for the responses.  What my ultimate objective is, I need my output file to look like what is indicated below.

The input dataset for the ATTRRECNO and MP.PHNUMBER looks like so:

MemberId       MemberLastName         MemberFirstNmae         ATTRRECNO        MP.PHNUMBER
1845555               Jones                             Rob                                  389               1308850932
1832333                Smith                           Anita                                  391               1582393838
3828383               Lee                               Bill                                     389               1308484848
1545558              Walker                          Lou                                    389                 1390838484
1545558             Walker                            Lou                                  391                   1309484838

I want my output results dataset to look like, or better yet, not display the ATTRRECNO field or the ATTRRECNO1 field at all.

MemberID MemberLastName MemberFirstName ATTRRECNO ATTRRECNO1 NEHII_HOME_PHONE NEHII_MOBILE_PHNUMBER

1845555     Jones                           Rob                        389                                   1308850932

1845555     Smith                           Anita                                               391                                                                             1582393838
3828383        Lee                           Bill                          389                                    1383994848
1545558        Walker                     Lou                         389                     391         1390838484                                            1309484838

Code is in code box.

24         proc sql noprint /*INOBS=100*/;
25         create table NED_Members as
26         
27         SELECT DISTINCT
28           MM.SEQ_MEMB_ID as MemberID
29         , MM.LAST_NAME as MemberLastName
30         , MM.FIRST_NAME as MemberFirstName
31         , MM.DATE_OF_BIRTH as MemberDOB
32         , MM.GENDER as M_F
33         , MM.MEDICAID_NO as MedicaidID
34         , MM.ETHNICITY_CODE
35         , MM.USER_DATE_7
36         , meh.line_of_business as LOB
37         , meh.SEQ_PROV_ID
38         , meh.IPA_ID
39         , MA.ADDRESS_LINE_1 AS ADDRESS1
40         , MA.ADDRESS_LINE_2 AS ADDRESS2
41         , MA.CITY AS CITY
42         , MA.STATE AS STATE
43         , MA.ZIP_CODE AS ZIP
44         , MA.COUNTY AS COUNTY
45         , MA.HOME_PHONE_NUMBER AS HOMEPHONE
46         , MEH.EFFECTIVE_DATE
47         , MEH.TERM_DATE
48         , MEH.PLAN_CODE
49         , PM.PROVIDER_ID
50         ,PM.LAST_NAME AS PROVIDER_LAST_NAME
51         ,PM.FIRST_NAME AS PROVIDER_FIRST_NAME
52         ,PM.PROVIDER_TYPE
53         ,PM.LICENSE
54         ,PA.COUNTY AS PROV_COUNTY
55         ,PA.ADDR_CATEGORY_CODE
2                                                          The SAS System                             09:13 Wednesday, June 19, 2019

56         ,MI.IDNUMBER AS SUBSCRIBER_ID
57         ,MP.ATTRRECNO AS ATTRRECNO
58         ,MP.ATTRRECNO AS ATTRRECNO1
59         ,MP.RECSTAT AS RECSTAT
60         ,MP.MEMRECNO AS MEMRECNO
61         , CASE
62             WHEN (MP.ATTRRECNO = 381) THEN MP.PHNUMBER
63             ELSE null
64         END AS NEHII_HOME_PHONE,
65         CASE
66             WHEN (MP.ATTRRECNO = 391) THEN MP.PHNUMBER
67             ELSE null
68         END AS NEHII_MOBILE_PHNUMBER
69         ,MP.PHNUMBER LABEL = "NEHII_MOBILE_PHNUMBER" AS NEHII_MOBILE_PHNUMBER
70         ,MAD.STLINE1 AS NEHII_STREET_ADDRESS1
71         ,MAD.CITY AS NEHII_CITY
72         ,MAD.STATE AS NEHII_STATE
73         ,MAD.COUNTY AS NEHII_COUNTY
74         ,MAD.ZIPCODE AS NEHII_ZIPCODE
75         
76         
77         FROM
78         HSDREPT.MEMBER_ADDRESS as MA
79         inner join HSDREPT.MEMBER_MASTER as MM
80             on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
81         inner join HSDREPT.member_elig_history  as meh
82             on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
83         inner join HSDREPT.PROV_MASTER as PM
84             on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID
85         inner join HSDREPT.PROV_ADDRESS as PA
86             on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
87         LEFT JOIN MDM.MPI_MEMIDENTWC  AS MI
88             on MM.MEDICAID_NO = MI.IDNUMBER
89         inner join MDM.MPI_MEMADDRWC as MAD
90             on MAD.MEMRECNO = MI.MEMRECNO
91         inner join MDM.MPI_MEMPHONEWC as MP
92             on MAD.MEMRECNO = MP.MEMRECNO
93         
94         WHERE
95         MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID
96         AND MA.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID
97         AND MEH.LINE_OF_BUSINESS = 'NED'

98         AND MEH.TERM_DATE IS NULL
99         AND MA.ADDRESS_TYPE = 'R5'
100        AND MA.TERM_DATE IS NULL
101        AND MAD.MEMRECNO = MI.MEMRECNO
102        AND MI.MEMRECNO = MP.MEMRECNO  AND MP.ATTRRECNO IN (389,391)
103        AND MP.MEMRECNO = MAD.MEMRECNO AND MAD.ATTRRECNO = 390;


ERROR: The following columns were not found in the contributing tables: null.

[req41273]

Sorry, 

the post messed up my output file.  The 391  under the ATTRRECNO variable should display the MP.PHNUMBER data under the NEHII_Mobile_Phnumber, and the 389 under the ATTRRECNO variable should display the MP.PHNUMBER data under the NEHII_HOMEPHONENUMBER.

[ScottBass]

the post messed up my output file.

Post your desired results using "Insert SAS Code" (even though it isn't SAS code) so that monospaced fonts are used and the output won't wrap - scrollbars are displayed instead.

Why don't you edit your "messed up post" and see for yourself 😉