DATA Step, Macro, Functions and more

if where clause

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 121
Accepted Solution

if where clause

Hi!  I need help troubleshooting what seems to be an easy conditional statement.  I need to create a table of married males, so I am using an if, then statement to output results.  However, I am getting zero observations once the syntax completes running (this is incorrect).

 

Here is the code I am using.

data want;

set have;

if MARITAL_STATUS eq 'MARRIED' and SPONSOR_SEX eq 'MALE' then output;

else delete;

run;

 

 

I've also tried using a where statement, and also got zero observations and getting this note

WHERE 0 /* an obviously FALSE WHERE clause */ ;

 

data want;

set have;

where MARITAL_STATUS eq 'MARRIED' and SPONSOR_SEX eq 'MALE';

run;

 

 

Any assistance is apprciated!!

 

 

 


Accepted Solutions
Solution
‎02-23-2016 05:08 PM
Super User
Posts: 17,776

Re: if where clause

Next step...proc contents on the dataset. Look for custom formats on the two variables.

 

Do you have a format applied that is displaying the variables as Male/Married, when it's actually 0/1 or 1/2/3/4 etc?

 

If so, you need to use the underlying values in your filtering query.

View solution in original post


All Replies
Super User
Posts: 17,776

Re: if where clause

Test your conditions with a proc freq

 

proc freq data=have;
table marital_status*sponsor_sex/missing;
run;

If you can't figure it out past here, post the output from the proc freq.

Frequent Contributor
Posts: 121

Re: if where clause

@Reeza the conditions seem fine

Table of MARITAL_STATUS by sponsor_sex
MARITAL_STATUS sponsor_sex
FEMALE MALE Total
 
0
0.00
0.00
0.00
40
0.00
100.00
0.00
40
0.00
 
 
ANNULLED
196
0.01
53.85
0.08
168
0.01
46.15
0.01
364
0.01
 
 
DIVORCED
49215
1.71
27.56
20.45
129382
4.49
72.44
4.90
178597
6.20
 
 
LEGALLY SEPARATED
444
0.02
18.98
0.18
1895
0.07
81.02
0.07
2339
0.08
 
 
MARRIED
151768
5.27
5.87
63.06
2434738
84.58
94.13
92.29
2586506
89.85
 
 
NEVER MARRIED
37612
1.31
35.03
15.63
69764
2.42
64.97
2.64
107376
3.73
 
 
WIDOWED
1425
0.05
40.94
0.59
2056
0.07
59.06
0.08
3481
0.12
 
 
Total
240660
8.36
2638043
91.64
2878703
100.00
Solution
‎02-23-2016 05:08 PM
Super User
Posts: 17,776

Re: if where clause

Next step...proc contents on the dataset. Look for custom formats on the two variables.

 

Do you have a format applied that is displaying the variables as Male/Married, when it's actually 0/1 or 1/2/3/4 etc?

 

If so, you need to use the underlying values in your filtering query.

Frequent Contributor
Posts: 121

Re: if where clause

@Reeza  ah, yes.  There is formatting applied.  So I need to use the original values before formatting?

Super User
Posts: 17,776

Re: if where clause

Yes

Super User
Posts: 17,776

Re: if where clause

Best marked answer I've received so far. If you can change it, I might consider changing correct answer to the one about running proc contents and checking for custom formats.

Frequent Contributor
Posts: 121

Re: if where clause

lol. "yes" is a great answer! I changed the solution Smiley Happy
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 475 views
  • 0 likes
  • 2 in conversation