Survey Select with Where Clause

Reply
Occasional Contributor
Posts: 6

Survey Select with Where Clause

Hello. The following code yields "NOTE: No observations were selected from data set <datasetname>:

proc surveyselect data=work.<datasetname> (where=(Flag_1 not in (.,0) and

                                                                  (amount_1 not in (.,0) or

                                                                  amount_2 not in (.,0) or

                                                                  amount_3 not in (.,0) or

                                                                  amount_4 not in (.,0))))

method=srs n=100 out=<location.filename>;

run;

Flag_1 is defined as char 1 and the amount fields are defined as numeric 8.

I have tried different syntax (gt (0)) instead of the above "not in (.,0)" but that has not worked either.  Any suggestions on how to improve this code?  I need the first condition, Flag_1, to be true when any of the other conditions are true.

Thanks so much.

Super User
Posts: 19,157

Re: Survey Select with Where Clause

Run a proc freq on Flag_1 to see the values in the field.

. or 0 is typical for numeric variables NOT character variables, though it could be, and if it is you need to quote those variables, flag_1 not in (".", "0").

If the variable  is character an equivalent might be (flag_1 not in ("", "0") and )

Occasional Contributor
Posts: 6

Re: Survey Select with Where Clause

Thank you! I tried to run the table as you suggested and received a "systems resource" warning so I did not proceed.

I use the full dataset (summarized) in a pivot table and confirmed that the condition that I am coding for exists. The survey select is meant to generate a sample for research.

Thanks for your suggestion!

Occasional Contributor
Posts: 6

Re: Survey Select with Where Clause

I followed your advice on the syntax for numerics vs. alpha and added the " " to Flag_1 which works when run on its own. However, when I add additional conditions, it continues to fail.

I know the condiition I am coding for does exist -- I see it in the pivot table based on the full file. I am trying to output a sample for research.

Thanks for your advice though. I am glad this community exists!

Super User
Posts: 11,134

Re: Survey Select with Where Clause

I would take a look at my data and verify that I have something that actually satisfies the condition. The following code will generate a , possibly very long, table of the combinations of data that appear in your data set.

Proc freq data=work.<datasetname>;

tables flag_1 * amount_1*amount_2*amount_3*amount_4/ missing list;

run;

Occasional Contributor
Posts: 6

Re: Survey Select with Where Clause

Thanks to everyone who looked at this question. I have not been able to resolve the issue with the survey select so I generated a sample by coding for it.

I know I will need this again in the future so will continue to try to figure it out.  I followed Reeza's advice on the syntax for numerics vs. alpha and added the " " to Flag_1 which works when run on its own. However, when I add additional conditions, it continues to fail.

I know the condiition I am coding for does exist -- I see it in the pivot table based on the full file. I am trying to output a sample for research.

Thank you both for your help. When I get this working, I will update the post.

SAS Super FREQ
Posts: 3,630

Re: Survey Select with Where Clause

Have you tried

data A;

set work.<datasetname>;

where=Flag_1 not in (.,0) and

      (amount_1 not in (.,0) or

      amount_2 not in (.,0) or

      amount_3 not in (.,0) or

      amount_4 not in (.,0));

run;

and just looked at what you get?  (Or look at first 200 obs if the data set is huge.)

Ask a Question
Discussion stats
  • 6 replies
  • 299 views
  • 0 likes
  • 4 in conversation