where statement with same condition on multiple variables

AliRKM — Wed, 22 Apr 2020 00:21:23 GMT

Hi all,

I have run a procsurvey freq on many variables and would like to print the CrossTabs where all variables have (or do not have) a specific value

Is there a way to stipulate something like:

proc print...;

where (var2 var2 var3) ne .;

This is a sample of the data - sorry so long but most people have subpop=0

data WORK.EXAMPLE;
infile datalines dsd truncover;

input DUPERSID:$8. REGIONY1:32. SEX:32. HISPANX:32. RTHLTH1:32. MNHLTH1:32. ADSMOK2:32. INSCOVY1:32. PMDINSY1:32. subpop:32. other_race:32. white:32. afr_am:32. a65_69:32. a70_74:32. a75_79:32. over80:32. married:32. div_sep:32. widowed:32. nev_marry:32. n
o_hs:32. hs_ed:32. more_hs:32. priv_ins:32. pub_only:32. no_ins:32. good_mhealth:32. norm_undwt:32. overwt:32. obese:32. vobese:32. polypharm:32.;

label DUPERSID="PERSON ID (DUID + PID)" REGIONY1="CENSUS REGION AS OF 12/31/13" SEX="SEX" HISPANX="HISPANIC ETHNICITY (EDITED/IMPUTED)" RTHLTH1="PERCEIVED HEALTH STATUS - RD 1" MNHLTH1="PERCEIVED MENTAL HEALTH STATUS - RD 1" ADSMOK2="SAQ: CURRENTLY SMOKE,R
2" INSCOVY1="HEALTH INSURANCE COVERAGE INDICATOR 13" PMDINSY1="PMED INSURANCE ANY TIME IN R3";
datalines;
10002101 1 1 2 3 3 1 2 2 1 0 0 1 1 0 0 0 0 1 0 0 0 1 0 0 1 0 1 0 1 0 0 1
10005101 4 2 2 3 3 2 2 2 0 0 0 1 1 0 0 0 0 1 0 0 0 0 1 0 1 0 1 0 0 0 1 1
10014101 1 2 2 4 4 2 1 1 0 0 1 0 1 0 0 0 0 1 0 0 0 0 1 1 0 0 0 1 0 0 0 1
10019102 2 2 2 3 3 2 1 2 0 0 1 0 0 1 0 0 0 0 1 0 0 0 1 1 0 0 1 0 0 1 0 1
10027101 1 1 2 3 1 2 2 2 0 0 1 0 0 0 0 1 1 0 0 0 0 1 0 0 1 0 1 1 0 0 0 1
10027102 1 2 2 5 3 2 2 2 1 0 1 0 0 0 0 1 1 0 0 0 1 0 0 0 1 0 1 0 0 0 1 1
10034101 3 1 2 2 2 -1 1 2 1 0 0 1 0 1 0 0 1 0 0 0 1 0 0 1 0 0 1 0 0 1 0 1
10034102 3 2 2 2 2 -1 1 2 0 0 0 1 . 0 0 0 1 0 0 0 1 0 0 1 0 0 1 0 1 0 0 1
10040101 4 2 2 3 2 2 1 1 1 1 0 0 0 1 0 0 0 0 0 1 0 0 1 1 0 0 1 0 1 0 0 0
10047101 2 1 2 1 2 2 1 1 0 0 1 0 0 1 0 0 0 0 1 0 0 1 0 1 0 0 1 0 0 1 0 1
10049101 1 1 2 2 2 2 2 2 0 0 0 1 . 0 0 0 0 0 0 1 0 1 0 0 1 0 1 . . . . 0
10056101 3 1 2 2 3 2 1 1 0 0 1 0 0 1 0 0 0 1 0 0 1 0 0 1 0 0 1 0 0 0 1 0
10058101 3 1 2 2 2 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 0 1 0 1 0 0 1 0 1 0 0 0
10058102 3 2 2 1 1 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10061101 1 1 2 2 2 2 2 2 0 0 1 0 0 0 1 0 1 0 0 0 0 1 0 0 1 0 1 0 1 0 0 1
10061102 1 2 2 4 4 2 2 2 1 0 1 0 0 0 1 0 1 0 0 0 0 1 0 0 1 0 0 0 0 1 0 1
10061102 1 2 2 4 4 2 2 2 1 0 1 0 0 0 1 0 1 0 0 0 0 1 0 0 1 0 0 0 0 1 0 1
10064101 4 2 2 3 2 2 2 2 0 1 0 0 0 0 1 0 1 0 0 0 0 1 0 0 1 0 1 1 0 0 0 0
10067101 3 2 2 4 4 2 2 2 1 0 1 0 0 0 0 1 0 0 0 1 0 1 0 0 1 0 0 1 0 0 0 1
10067102 3 2 2 5 4 2 2 2 0 0 1 0 0 0 0 1 0 0 0 1 1 0 0 0 1 0 0 1 0 0 0 1
10068101 2 2 2 3 3 2 1 1 0 0 1 0 0 0 1 0 0 0 1 0 0 0 1 1 0 0 1 0 0 0 1 1
10076101 1 2 2 1 1 2 1 1 0 0 1 0 0 1 0 0 0 1 0 0 0 0 1 1 0 0 1 1 0 0 0 1
10079101 4 2 2 4 3 2 1 1 1 0 1 0 1 0 0 0 0 1 0 0 0 0 1 1 0 0 1 0 1 0 0 1
10083101 2 1 2 2 2 2 2 2 0 0 1 0 1 0 0 0 1 0 0 0 0 1 0 0 1 0 1 0 0 1 0 0
10083102 2 2 2 2 2 2 2 2 0 0 1 0 1 0 0 0 1 0 0 0 0 1 0 0 1 0 1 0 0 1 0 0
10086101 4 1 2 2 2 2 1 1 0 1 0 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10111101 2 1 2 5 2 2 1 1 0 0 1 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 0 0 1 0 1
10111102 2 2 2 1 2 2 1 1 0 0 1 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10112101 3 1 2 3 3 -9 2 2 1 0 0 1 0 0 0 1 0 0 1 0 1 0 0 0 1 0 1 0 0 0 1 0
10115101 4 1 2 3 1 2 2 2 0 0 1 0 0 0 1 0 1 0 0 0 0 0 1 0 1 0 1 0 1 0 0 0
10115102 4 2 2 2 1 2 2 2 0 0 1 0 0 1 0 0 1 0 0 0 0 0 1 0 1 0 1 1 0 0 0 0
3 The SAS System 12:01 Tuesday, April 21, 2020

10117101 3 1 2 3 3 2 1 2 0 0 1 0 0 0 0 1 0 0 1 0 . . . 1 0 0 1 0 1 0 0 1
10118101 3 2 2 3 2 1 1 1 0 0 1 0 . 0 0 0 0 0 1 0 1 0 0 1 0 0 1 0 1 0 0 0
10120101 1 1 2 2 2 2 1 2 0 1 0 0 0 1 0 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10120102 1 2 2 1 1 2 1 2 0 1 0 0 1 0 0 0 1 0 0 0 0 1 0 1 0 0 1 1 0 0 0 1
10126101 2 1 2 2 1 2 2 2 0 0 1 0 0 1 0 0 0 1 0 0 0 0 1 0 1 0 1 1 0 0 0 1
10130101 4 1 2 4 4 2 1 2 0 1 0 0 0 0 0 1 1 0 0 0 0 1 0 1 0 0 0 1 0 0 0 0
10130102 4 2 2 3 2 2 1 2 0 1 0 0 0 0 1 0 1 0 0 0 0 1 0 1 0 0 1 1 0 0 0 0
10135101 4 1 2 4 1 2 2 2 0 0 1 0 1 0 0 0 1 0 0 0 0 0 1 0 1 0 1 1 0 0 0 1
10135102 4 2 2 2 1 2 2 2 0 0 1 0 1 0 0 0 1 0 0 0 0 1 0 0 1 0 1 0 1 0 0 1
10136102 3 1 2 3 3 2 1 1 1 0 1 0 0 0 1 0 0 0 1 0 0 1 0 1 0 0 1 0 1 0 0 1
10138101 4 1 2 2 2 2 1 1 0 0 1 0 0 0 1 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10138102 4 2 2 2 2 2 1 1 0 1 0 0 0 0 1 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10140101 2 1 2 2 3 -1 1 2 0 0 1 0 1 0 0 0 1 0 0 0 1 0 0 1 0 0 1 0 1 0 0 0
10140102 2 2 2 3 3 -1 1 2 0 0 1 0 1 0 0 0 1 0 0 0 1 0 0 1 0 0 1 0 1 0 0 0
10144101 3 2 2 3 3 2 2 2 0 0 0 1 0 0 1 0 0 0 1 0 0 1 0 0 1 0 1 1 0 0 0 0
10146101 3 2 2 2 2 2 2 2 0 1 0 0 1 0 0 0 0 0 1 0 0 1 0 0 1 0 1 1 0 0 0 1
10154101 4 1 1 1 1 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 0 1 0 1 0 0 1 0 1 0 0 0
10154104 4 2 1 4 3 2 3 2 0 0 1 0 0 0 0 1 0 0 1 0 1 0 0 0 0 1 1 1 0 0 0 1
10156103 3 2 1 4 1 2 2 2 0 0 1 0 0 1 0 0 0 0 0 1 1 0 0 0 1 0 1 0 1 0 0 0
10157101 1 1 2 3 2 2 2 2 1 0 1 0 0 0 0 1 0 0 1 0 0 0 1 0 1 0 1 0 1 0 0 1
10159101 4 2 2 2 2 2 1 2 0 0 1 0 1 0 0 0 0 1 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10162101 2 2 2 3 2 2 2 2 0 0 1 0 0 0 1 0 0 1 0 0 0 0 1 0 1 0 1 1 0 0 0 1
10166101 3 1 2 3 3 2 1 1 0 0 1 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 0 1 0 0 1
10166102 3 2 2 3 3 2 1 1 0 0 1 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 0 1 0 0 0
10168102 4 1 1 3 3 2 2 2 0 0 1 0 . 0 0 0 1 0 0 0 0 0 1 0 1 0 1 0 1 0 0 1
10169102 4 1 2 2 2 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 0 0 1 1 0 0 1 0 1 0 0 0
10175101 4 1 2 2 3 1 2 2 0 1 0 0 . 0 0 0 1 0 0 0 0 1 0 0 1 0 1 1 0 0 0 0
10180101 1 2 2 4 4 1 2 2 1 0 0 1 0 1 0 0 0 0 1 0 1 0 0 0 1 0 0 1 0 0 0 1
10181101 3 1 2 2 2 2 1 2 0 0 1 0 0 0 1 0 1 0 0 0 0 0 1 1 0 0 1 0 0 0 1 1
10181102 3 2 2 4 3 2 1 2 0 1 0 0 0 0 0 1 1 0 0 0 0 1 0 1 0 0 1 1 0 0 0 1
10183101 1 2 2 1 1 2 1 1 0 0 1 0 . 0 0 0 0 0 1 0 0 1 0 1 0 0 1 1 0 0 0 0
10184101 1 1 1 2 2 2 2 2 1 1 0 0 1 0 0 0 1 0 0 0 0 1 0 0 1 0 1 0 0 1 0 0
10200101 2 1 2 5 5 1 2 2 0 0 1 0 . 0 0 0 0 0 0 1 1 0 0 0 1 0 0 1 0 0 0 1
10206101 1 1 2 5 2 2 1 2 0 0 1 0 0 0 0 1 1 0 0 0 0 1 0 1 0 0 1 1 0 0 0 1
10206102 1 2 2 3 3 2 1 2 0 0 1 0 0 0 0 1 1 0 0 0 1 0 0 1 0 0 1 0 1 0 0 1
10209101 4 2 1 3 3 -1 1 1 0 0 1 0 . 0 0 0 0 1 0 0 0 0 1 1 0 0 1 0 1 0 0 0
10214101 3 1 2 4 4 2 1 1 0 0 0 1 . 0 0 0 1 0 0 0 1 0 0 1 0 0 0 0 1 0 0 1
10220101 2 2 2 2 2 2 2 2 0 0 1 0 0 1 0 0 1 0 0 0 0 1 0 0 1 0 1 1 0 0 0 0
10220102 2 1 2 2 3 2 2 2 0 0 1 0 0 0 1 0 1 0 0 0 0 1 0 0 1 0 1 0 0 0 1 0
10221101 3 1 1 3 1 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 1 0 0 1 0 0 1 0 0 1 0 0
10224101 4 1 2 2 1 2 1 1 1 0 1 0 0 0 0 1 1 0 0 0 0 1 0 1 0 0 1 0 1 0 0 1
10224102 4 2 2 1 1 2 1 2 0 0 1 0 0 0 1 0 1 0 0 0 0 1 0 1 0 0 1 0 1 0 0 0
10243101 4 2 1 3 1 2 2 2 0 0 1 0 . 0 0 0 0 1 0 0 0 1 0 0 1 0 1 0 0 1 0 1
10245101 3 1 2 1 1 2 1 1 0 0 1 0 0 1 0 0 0 0 1 0 0 0 1 1 0 0 1 0 1 0 0 0
10246101 4 2 2 3 3 1 2 2 0 0 1 0 1 0 0 0 1 0 0 0 0 1 0 0 1 0 1 0 1 0 0 0
10246102 4 1 2 4 3 1 2 2 0 0 1 0 1 0 0 0 1 0 0 0 1 0 0 0 1 0 1 0 1 0 0 1
10247101 2 1 2 4 2 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 0 0 1 1 0 0 1 0 0 1 0 0
10247102 2 2 2 4 4 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 0 1 0 1 0 0 0 0 1 0 0 0
10248101 2 1 2 3 3 2 2 2 0 1 0 0 . 0 0 0 1 0 0 0 1 0 0 0 1 0 1 1 0 0 0 0
10250101 1 2 2 2 3 2 2 2 0 0 1 0 0 1 0 0 0 0 1 0 0 1 0 0 1 0 1 1 0 0 0 1
10251101 2 1 2 4 1 2 1 2 0 0 1 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 0 1 0 0 0
10251102 2 2 2 3 1 2 1 2 0 0 1 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 0 1 0 0 1
10253101 3 2 2 2 1 1 1 1 0 0 0 1 . 0 0 0 0 0 1 0 0 1 0 1 0 0 1 1 0 0 0 0
10254101 3 1 2 3 2 2 1 1 0 0 1 0 0 1 0 0 0 0 1 0 0 0 1 1 0 0 1 0 0 1 0 1
10257101 3 1 1 4 1 -1 2 2 0 0 1 0 0 0 1 0 0 1 0 0 0 0 1 0 1 0 1 0 0 0 1 0
10260101 4 1 2 1 1 -1 1 1 0 1 0 0 0 1 0 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10260102 4 2 2 1 1 -1 1 1 0 1 0 0 1 0 0 0 1 0 0 0 0 0 1 1 0 0 1 1 0 0 0 0
10261101 3 1 2 1 1 2 1 1 0 0 1 0 . 0 0 0 1 0 0 0 0 1 0 1 0 0 1 0 0 0 1 0
4 The SAS System 12:01 Tuesday, April 21, 2020

10269101 4 1 1 2 3 2 1 2 0 0 1 0 0 1 0 0 1 0 0 0 1 0 0 1 0 0 1 0 1 0 0 0
10274103 3 1 2 2 1 -1 2 2 0 0 1 0 0 1 0 0 0 1 0 0 . . . 0 1 0 1 1 0 0 0 0
10277101 3 1 2 2 2 2 1 1 0 0 1 0 0 0 0 1 0 0 1 0 0 1 0 1 0 0 1 0 1 0 0 0
10286101 4 1 2 4 3 2 2 2 1 0 0 1 1 0 0 0 0 1 0 0 0 0 1 0 1 0 1 1 0 0 0 1
10292201 2 1 2 1 1 1 2 2 1 0 1 0 0 1 0 0 0 1 0 0 0 1 0 0 1 0 1 0 0 1 0 0
10302101 3 2 2 2 2 2 3 2 0 0 1 0 . 0 0 0 1 0 0 0 0 1 0 0 0 1 1 0 0 1 0 0
10302102 3 1 2 2 2 2 1 2 0 0 1 0 . 0 0 0 1 0 0 0 0 1 0 1 0 0 1 0 1 0 0 0
10305101 2 2 2 3 2 2 2 2 0 0 1 0 0 1 0 0 0 0 1 0 0 1 0 0 1 0 1 0 0 1 0 1
10311101 3 2 2 3 3 2 2 2 0 0 0 1 1 0 0 0 0 1 0 0 0 0 1 0 1 0 1 0 0 0 1 1
10316101 2 2 2 4 4 1 2 2 1 0 1 0 1 0 0 0 0 1 0 0 0 1 0 0 1 0 0 0 0 0 1 1
10327101 2 2 2 3 2 2 1 2 0 0 1 0 0 0 0 1 0 0 1 0 0 1 0 1 0 0 1 0 1 0 0 1
;;;;

I tried this with no luck

where subpop=1 and (F_a65_69 -- pmdinsy1 ne .);

Maybe an array but isn't that only in data steps?

As you can see form below, my variable names have no pattern to them.

ods output Crosstabs=out;
proc surveyfreq data=meps.analysis;
strata varstr;
cluster varpsu;
weight poolwt;
tables subpop*(a65_69 a70_74 a75_79 over80 sex white afr_am other_race hispanx  married div_sep widowed nev_marry no_hs hs_ed more_hs inscovy1   rthlth1  mnhlth1 norm_undwt overwt obese vobese  adsmok2 curr_smoke polypharm   pmdinsy1 );
run;

proc print data=out;
where subpop=1;
var Frequency WgtFreq StdDeva65_69 a70_74 a75_79 over80 sex white afr_am other_race hispanx  married div_sep widowed nev_marry no_hs hs_ed more_hs inscovy1   rthlth1  mnhlth1 norm_undwt overwt obese vobese  adsmok2 curr_smoke polypharm   pmdinsy1;
run;

Re: where statement with same condition on multiple variables

PaigeMiller — Wed, 22 Apr 2020 01:39:31 GMT

Instead of

where subpop=1 and (F_a65_69 -- pmdinsy1 ne .);

How about

where subpop=1 and nmiss(of F_a65_69 -- pmdinsy1) = 0;

Re: where statement with same condition on multiple variables

AliRKM — Wed, 22 Apr 2020 02:11:23 GMT

No luck: Got this in the log:

26 proc print data=out;
27 where subpop=1 and nmiss(of F_a65_69 -- pmdinsy1) = 0;
________
22
76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, GE, GT, LE, LIKE, LT, NE, OR, ^=, |, ||, ~=.
ERROR 76-322: Syntax error, statement will be ignored.

Tried parenthesis:
where subpop=1 and nmiss(of (F_a65_69 -- pmdinsy1)) = 0;

and got this:
ERROR: WHERE clause operator requires numeric variables.

Re: where statement with same condition on multiple variables

Tom — Wed, 22 Apr 2020 03:04:10 GMT

You cannot use variable lists in WHERE statement. They essentially follow the SQL rules, not the regular SAS rules. Positional variable lists would probably be difficult anyway.

You can use an IF statement in a DATA step.

data x;
 set sashelp.class;
 if cmiss(of name -- weight);
run;

Re: where statement with same condition on multiple variables

PGStats — Wed, 22 Apr 2020 04:28:07 GMT

You can always use a data view like this:

data mySubset / view=mySubset;
set out;
if subpop=1 and cmiss(F_a65_69 -- pmdinsy1) = 0;
run;

proc print data=mySubset; ...

Re: where statement with same condition on multiple variables

AliRKM — Wed, 22 Apr 2020 15:10:20 GMT

Still no luck:

NOTE: A stored DATA STEP view cannot run under a different operating system.

the output is blank.

I tried to read up on the note but did not understand the comments. Nor do I understand what a data view is - again, despite reading up on it. I have no technical background, my statistics is shaky so this is all beyond me 😞

Thanks for your help.

topic Re: where statement with same condition on multiple variables in SAS Procedures

where statement with same condition on multiple variables

Re: where statement with same condition on multiple variables

Re: where statement with same condition on multiple variables

Re: where statement with same condition on multiple variables

Re: where statement with same condition on multiple variables

Re: where statement with same condition on multiple variables