BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DmytroYermak
Lapis Lazuli | Level 10

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

@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
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

DmytroYermak
Lapis Lazuli | Level 10
Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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