DATA Step, Macro, Functions and more

where statement with missing char data points

Reply
Contributor
Posts: 25

where statement with missing char data points

Trying to print out the 3 variables when Char_DOB is = to a . in the data sets. (it is invalid so that's why its blank)

 

anyways I get
573 where Char_DOB=.;
ERROR: WHERE clause operator requires compatible variables.

 

when I run the proc.

 

 

proc print data=hi.student;
where Char_DOB=.;
var SSN Total_DOB Char_DOB;
run;

Super User
Posts: 19,770

Re: where statement with missing char data points

Empty for a character variable is a space. 

 

The MISSING() function works for either numeric or character fields. 

 

Where missing(char_dob);

Contributor
Posts: 25

Re: where statement with missing char data points

When I try either of those I get:

 

NOTE: No observations were selected from data set HI.STUDENT.
NOTE: There were 0 observations read from the data set HI.STUDENT.
WHERE Char_DOB=' ';
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

 

or same for the missing function.

Super User
Posts: 19,770

Re: where statement with missing char data points

Are you sure you have missing values then? 

 

Contributor
Posts: 25

Re: where statement with missing char data points

The Char_DOB has invalid dates which arent showing and they are the observations I want to show.

Super User
Posts: 19,770

Re: where statement with missing char data points

Please show a proc contents for the dataset, an image of your invalid data and the code and log. 

Contributor
Posts: 25

Re: where statement with missing char data points

Here is my full code

 

libname hi
'C:\Users\larkj\Desktop';
proc format library = hi;
value season
1='Fall' 2='Spring' 3='Summer';
value $sx
'M'='Male' 'F'='Female';
value race
.='Missing' 2='Hispanic' 3='Puerto Rican'
4='Mexican American' 5='Asian American'
6='Caucasian' 7='American Indian' 8='African American'
9='Other';
;
run;
options fmtsearch=(hi);
data hi.student (drop=i);
infile 'C:\Users\larkj\Desktop\STUDENT.DAT';
input ssn 1-9 sex $ 11 term 17 type $ 19 div $ 21-24
major 26-30 ed $ 32 state $ 34-35 sat 37-40 satm 42-44
satv 46-48 gpa 50-53 rank 55-58 act 60-63 toefl 65-68
honor $ 70 ethnic 72 year_dob 101-102 month_dob 103-104
day_dob 105-106
;
Char_DOB = input(catx("/", month_dob, day_dob, year_dob),?? mmddyy8.);
Total_DOB = input(catx("/", month_dob, day_dob, year_dob),?? mmddyy8.);
if ((month_dob = 02) AND (day_dob= 29)) then Total_DOB = mdy(03,01,year_dob)-1;
else Total_DOB = mdy(month_dob,15,year_dob);
format Total_DOB mmddyy8.
term Season.
sex $Sx.
ethnic Race.;
array change (6) gpa sat satm satv act toefl;
do i = 1 TO 6;
if change(i) = 0 then change(i) = .;
end;
Char_DOBc = put(Char_DOB,mmddyy8.);
drop Char_DOB;
rename Char_DOBc = Char_DOB;
run;
PROC CONTENTS DATA=hi.student;
RUN;
proc print data=hi.student;
format term Season. sex $Sx. ethnic Race. Total_DOB mmddyy8. ;
run;
proc print data=hi.student;
where Char_DOB= ' ';
var SSN Total_DOB Char_DOB;
run;

 

 

Super User
Posts: 5,424

Re: where statement with missing char data points

Seems that you are not telling us the full story. Since it's a char variable, no values can be seen as invalid from data storage perspective. An invalid date stored as char won't necessary be missing.

Or, if you have moved a date value from a numeric column using a date format, then you might end up with the char '.' (which is not missing for char variable).

Data never sleeps
Ask a Question
Discussion stats
  • 7 replies
  • 182 views
  • 0 likes
  • 3 in conversation