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
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.
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.
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 wrote:
IS NULL isn't valid SAS syntax.
You can use MISSING() instead
And missing(MEH.TERM_DATE)
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.
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;
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.
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.
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 😉
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.