## Proc Freq: two variable without missing values

Solved
Regular Contributor
Posts: 214

# Proc Freq: two variable without missing values

Hi all,

Could you please help not to take into account 'missing' when perform 'proc freq' for two variables. For instance for the following code:

``````data one;
input Patient : \$1 Visit1 : \$3. EndOfStudy : \$3. ;
datalines;
1 NORM ABN
2 ABN ABN
3 MISS NORM
4 NORM NORM
6 NORM ABN
7 NORM MISS
8 ABN ABN
9 NORM ABN
10 MISS ABN
11 NORM ABN
12 MISS ABN
13 NORM NORM
14 ABN ABN
15 NORM ABN
16 NORM NORM
17 NORM ABN
18 MISS ABN
19 ABN ABN
20 NORM ABN
21 NORM ABN
22 ABN NORM
23 MISS ABN
24 NORM ABN
25 ABN NORM
26 NORM ABN
27 MISS ABN
28 ABN NORM
29 NORM ABN
;
run;``````

Thus I have the following dataset:

Then proc freq itself:

``````ods output CrossTabFreqs=one1;
proc freq data=one;
table Visit1*EndOfStudy;
run;``````

And the result where I don't need lines with missing values:

The question is: what is the option of 'proc freq' not to take into account combinations with 'missing' values?

Thank you!

Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 9,599

## Re: Proc Freq: two variable without missing values

Posted in reply to DmytroYermak

Could it be as simple as:

``````ods output CrossTabFreqs=one1;
proc freq data=one (where=(visit1 ne . or endofstudy ne .));
table Visit1*EndOfStudy;
run;``````

All Replies
Solution
3 weeks ago
Super User
Posts: 9,599

## Re: Proc Freq: two variable without missing values

Posted in reply to DmytroYermak

Could it be as simple as:

``````ods output CrossTabFreqs=one1;
proc freq data=one (where=(visit1 ne . or endofstudy ne .));
table Visit1*EndOfStudy;
run;``````

Super User
Posts: 10,217

## Re: Proc Freq: two variable without missing values

@RW9 wrote:

Could it be as simple as:

``ods output CrossTabFreqs=one1; proc freq data=one (where=(visit1 ne . or endofstudy ne .)); table Visit1*EndOfStudy; run;``

Hmm. Applied to the presented example data, this throws an ERROR:

```25         proc freq data=one(where=(visit1 ne . or endofstudy ne .));
ERROR: WHERE clause operator requires compatible variables.
26          table Visit1*EndOfStudy;
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.
27         run;
```

And even when the type is corrected:

``````ods output CrossTabFreqs=one1;
proc freq data=one(where=(visit1 ne "" or endofstudy ne ""));
table Visit1*EndOfStudy;
run;``````

the output dataset from the ods output contains the unwanted rows:

```Table Visit1 * EndOfStudy	ABN	ABN	11	1	4	14.29	57.14	20.00	.
Table Visit1 * EndOfStudy	ABN	MIS	11	1	0	0.00	0.00	0.00	.
Table Visit1 * EndOfStudy	ABN	NOR	11	1	3	10.71	42.86	42.86	.
Table Visit1 * EndOfStudy	ABN		10	1	7	25.00	.	.	.
Table Visit1 * EndOfStudy	MIS	ABN	11	1	5	17.86	83.33	25.00	.
Table Visit1 * EndOfStudy	MIS	MIS	11	1	0	0.00	0.00	0.00	.
Table Visit1 * EndOfStudy	MIS	NOR	11	1	1	3.57	16.67	14.29	.
Table Visit1 * EndOfStudy	MIS		10	1	6	21.43	.	.	.
Table Visit1 * EndOfStudy	NOR	ABN	11	1	11	39.29	73.33	55.00	.
Table Visit1 * EndOfStudy	NOR	MIS	11	1	1	3.57	6.67	100.00	.
Table Visit1 * EndOfStudy	NOR	NOR	11	1	3	10.71	20.00	42.86	.
Table Visit1 * EndOfStudy	NOR		10	1	15	53.57	.	.	.
Table Visit1 * EndOfStudy		ABN	01	1	20	71.43	.	.	.
Table Visit1 * EndOfStudy		MIS	01	1	1	3.57	.	.	.
Table Visit1 * EndOfStudy		NOR	01	1	7	25.00	.	.	.
Table Visit1 * EndOfStudy			00	1	28	100.00	.	.	0```
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 9,599

## Re: Proc Freq: two variable without missing values

Posted in reply to KurtBremser

Didn't really look at the data.  No time to check, but maybe:

``````proc freq data=one(where=(visit1 ne "" and endofstudy ne ""));
``````

I do like you solution however, but it still going to do the check, just filtering out afterwards, so bit of a waste of calculation time.  But I doubt it matters in this instance.

Super User
Posts: 10,217

## Re: Proc Freq: two variable without missing values

Posted in reply to DmytroYermak

Add a where= dataset option:

``ods output CrossTabFreqs=one1 (where=(endofstudy ne ""));``
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 214

## Re: Proc Freq: two variable without missing values

Posted in reply to KurtBremser
Thank you!
☑ This topic is solved.

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

Discussion stats
• 5 replies
• 63 views
• 2 likes
• 3 in conversation