topic Re: How to merger proc freq on multiple columns of a table in SAS Programming

How to merger proc freq on multiple columns of a table

ralizadeh — Tue, 07 Mar 2023 19:10:27 GMT

The codes below are to run two separate proc freq on two columns of the same table.

proc freq data=asamtable_2;
	tables rectype /nocum nopercent;
	where rectype not in ("Brief Initial Screen" 
			      "Followup Assessment" 
			      "Initial Assessment");

proc freq data=asamtable_2;
	tables Delay_Rsn /nocum nopercent;
	where Delay_Rsn not in (
                                 "Waiting for level of care availability"
                                 "Waiting for language-specific services"
				 "Waiting for other special popn-specific svcs"
				 "Hospitalized"
				 "Incarcerated"
				 "Patient preference"
				 "Waiting for ADA accommodation"
				 "Other");
run;

And I get this:

Question is: What would be the best way to merge these two separate codes into one code (basically run one code on TWO columns) in SAS Studio to get the same result? How would you do it in SAS EG?

Any help would be appreciated.

Re: How to merger proc freq on multiple columns of a table

ballardw — Tue, 07 Mar 2023 19:23:33 GMT

The WHERE statement subsets observations for the procedure. So you can't get what you want with a single Proc freq and WHERE statements because the counts would be wrong.

Please describe the concern about trying to run the results in "one code".

Re: How to merger proc freq on multiple columns of a table

ralizadeh — Tue, 07 Mar 2023 20:20:32 GMT

Looks like you did understand my "one code" comments. 🙂 Yes, I wanted a single Proc freq and WHERE statements.

What are the alternatives if that can't be done this way? Are there any other proc steps I might use to obtain what I want?

Re: How to merger proc freq on multiple columns of a table

Reeza — Tue, 07 Mar 2023 20:57:41 GMT

Do a single ODS table and then filter the results afterwards. This only works if you want counts, the percentages will not be correct.

*Run frequency for tables;
ods table onewayfreqs=temp;
proc freq data=sashelp.class;
	table sex age;
run;

*Format output;
data want;
length variable $32. variable_value $50.;
set temp;
Variable=scan(table, 2);

Variable_Value=strip(trim(vvaluex(variable)));

keep variable variable_value frequency percent cum:;
label variable='Variable' 
	variable_value='Variable Value';
run;

*Display;
*Display;
proc print data=want label;
where (variable = 'Age' and variable_value not in ('10', '11', '13', '14')) | (variable = 'Sex' and variable_value not in ('M'));
var variable variable_value frequency;
run;

@ralizadeh wrote:

The codes below are to run two separate proc freq on two columns of the same table.
proc freq data=asamtable_2;
	tables rectype /nocum nopercent;
	where rectype not in ("Brief Initial Screen" 
			      "Followup Assessment" 
			      "Initial Assessment");
proc freq data=asamtable_2;
	tables Delay_Rsn /nocum nopercent;
	where Delay_Rsn not in (
                                 "Waiting for level of care availability"
                                 "Waiting for language-specific services"
				 "Waiting for other special popn-specific svcs"
				 "Hospitalized"
				 "Incarcerated"
				 "Patient preference"
				 "Waiting for ADA accommodation"
				 "Other");
run;
And I get this:

Question is: What would be the best way to merge these two separate codes into one code (basically run one code on TWO columns) in SAS Studio to get the same result? How would you do it in SAS EG?

Any help would be appreciated.

Re: How to merger proc freq on multiple columns of a table

ralizadeh — Tue, 07 Mar 2023 21:04:54 GMT

@Reeza! Thank you for the suggestion, however that wasn't what I was looking for. Despite the fact that your codes will merge two frequency tables into one, they still do not answer my question. In my code, there is a WHERE statement for each column, and they are distinct. I need a method that keeps the two conditions true for their respective columns.

Re: How to merger proc freq on multiple columns of a table

Reeza — Tue, 07 Mar 2023 21:11:02 GMT

See the edit, you can implement your WHERE on the final data set rather than on the PROC FREQ.

Re: How to merger proc freq on multiple columns of a table

Tom — Tue, 07 Mar 2023 21:15:30 GMT

Transpose the data first. Then you can run your PROC FREQ on the transposed data.

To use PROC TRANSPOSE you will need to variable (or set of variables) you can use to uniquely identify the rows with a BY statement.

proc transpose data=asamtable_2 out=TALL ;
   by ID ;
   var rectype Delay_Rsn ;
run;

Otherwise use a data step to do the transpose (which could be a VIEW if you want);

data TALL ;
  set asamtable_2'
  length _name_ $32 col1 $100 ;
  _name_='rectype';col1=rectype; output;
  _name_='delay_rsn';col1=Delay_Rsn; output;
run;

Now you can make a WHERE statement that tests both variables

proc freq data=TALL;
	tables _name_*col1 / list ;
where (_name_='rectype' and col1 not in ("Brief Initial Screen" 
			      "Followup Assessment" 
			      "Initial Assessment"
      ))
   or (_name_='delay_rsn' and col1 not in (
                 "Waiting for level of care availability"
                 "Waiting for language-specific services"
				 "Waiting for other special popn-specific svcs"
				 "Hospitalized"
				 "Incarcerated"
				 "Patient preference"
				 "Waiting for ADA accommodation"
				 "Other"
       ))
;
run;

Re: How to merger proc freq on multiple columns of a table

ralizadeh — Tue, 07 Mar 2023 21:38:23 GMT

Thank you @Tom! Very much appreciated!

Re: How to merger proc freq on multiple columns of a table

ballardw — Tue, 07 Mar 2023 22:05:18 GMT

@ralizadeh wrote:

Looks like you did understand my "one code" comments. 🙂 Yes, I wanted a single Proc freq and WHERE statements.

What are the alternatives if that can't be done this way? Are there any other proc steps I might use to obtain what I want?

Still haven't explained why the need, seems cumbersome.

Create a new data set, set the values you don't want to missing.

Then call proc freq with the two variables but NO where statements.

Counts and percents should match the desired result, there will be a count of missing displayed, which may be useful. Also you can use these variables in two-way (or more-way) tables.

I think this may be a lot less wor