Re: where statement with same condition on multiple variables

AliRKM · Posted 04-21-2020 08:21 PM

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;

PaigeMiller · Posted 04-21-2020 09:39 PM

Instead of

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

How about

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

--
Paige Miller

AliRKM · Posted 04-21-2020 10:11 PM

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.

Tom · Posted 04-21-2020 11:04 PM

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;

PGStats · Posted 04-22-2020 12:28 AM

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; ...

PG

AliRKM · Posted 04-22-2020 11:10 AM

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.

where statement with same condition on multiple variables