DATA Step, Macro, Functions and more

Proc Freq: two variable without missing values

Accepted Solution Solved
Reply
Regular Contributor
Posts: 214
Accepted Solution

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:

2.jpg

 

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:

1.jpg

 

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

 

View solution in original post


All Replies
Solution
3 weeks ago
Super User
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
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