0

All my missing values are being deleted when I do not wish them to be. I am assuming SAS and Oracle are not playing nice - but I do not know the solution. Thank-you if you can offer any ideas about this error.

Observe the character field SEX has missing values: 

data test ;
  input name $ sex $ age city $;
  if sex = "NA" then sex=' ';
  if city = "Unk" then city = ' ';
  cards;
Gene  M  62 Saginaw
Cyndi F    45 Unk 
Alice  NA  51 Bay City       
Bob   M   55 Unk
;
proc print data=test; run;

enter image description here

Observe that I can filter on SEX and still have the missing value: 

proc sql; 
create table que1 as 
select * from test where sex 
not in ('F','M'); 
quit;
proc sql; select * from que1; quit;

enter image description here

Observe a vanilla data pull from an Oracle table via a libname connection: 

proc sql; 
create table test as 
select * from dss.student_registrations
where term_code gt '201500'
  and row_type = 'E'
/*  and final_grade not in ('AU','WU') */
;quit;
proc freq data=test; tables final_grade / missing; run;

enter image description here

Now I place the limit on final_grade and all the missing values disappear: 

proc sql; 
create table test as 
select * from dss.student_registrations
where term_code gt '201500'
  and row_type = 'E'
  and final_grade not in ('AU','WU')
;quit;
proc freq data=test; tables final_grade / missing; run;

enter image description here

Wes McClintick
  • 497
  • 5
  • 14

1 Answers1

3

SAS has binary logic (true/false). If Final_Grade is null in SAS, Final_Grade NOT IN ('AU','WU') will be true. Oracle has trinary logic (true/false/unknown). If Final_Grade is null in Oracle, Final_Grade NOT IN ('AU','WU') will be unknown.

Although you are writing SAS code, it's passing the code through to Oracle to process. Suggest you try:

where term_code gt '201500'
and row_type = 'E'
and (final_grade not in ('AU','WU') or final_grade is Null)

This and many other gotchas for SAS programmers reading from RDBMS are addressed in a nice paper by Mike Rhoads, http://support.sas.com/resources/papers/proceedings09/141-2009.pdf.

Quentin
  • 5,960
  • 1
  • 13
  • 21
  • Well, that's interesting. From what I've read, sometimes SAS processes / sometimes Oracle processes depending on whether or not you write an Oracle compatible query (no SAS specific language). If I used a SAS specific function, I suppose it would return the expected result. I'll have to play around with it a little. Thank-you. – Wes McClintick Mar 04 '16 at 22:15
  • This is true. I usually avoid implicit pass through for that reason. I like to control whether SAS is doing the processing or the remote database. – Quentin Mar 04 '16 at 22:16
  • To force SAS to do all processing, use option in libname statement: direct_sql = none ... per http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#p1hrfk5zjg3mi2n16xnrbmf38m2f.htm – Wes McClintick Mar 05 '16 at 01:08