Proc Freq: two variable without missing values

[DmytroYermak]

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!

[RW9 · Accepted solution]

Could it be as simple as:

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

[Kurt_Bremser]

---

@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

[RW9]

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.

[Kurt_Bremser]

Add a where= dataset option:

ods output CrossTabFreqs=one1 (where=(endofstudy ne ""));

[DmytroYermak]

Thank you!