- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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; ...
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.