topic Re: How to specify list of variables in a where statement in proc freq in SAS Procedures

How to specify list of variables in a where statement in proc freq

Angmar — Fri, 24 Jun 2022 15:01:05 GMT

Is it possible to specify a list of variables in a 'where' statement in proc freq without listing them all?

I want to specify variables diag_code_1 to diag_code_25, but using a hyphen doesn't work.

PROC FREQ DATA=LIB.LIB21 (where=(diag_code_1-diag_code_25 not like 'j45'));
TABLE year;
RUN;

Thank you

Reeza — Fri, 24 Jun 2022 15:14:25 GMT

PROC FREQ DATA=LIB.LIB21 (where=find((catx("|", of diag_code_1-diag_code_25), 'J45', 'it') >0));
TABLE year;
RUN;

Something like this?

Tom — Fri, 24 Jun 2022 15:16:24 GMT

You cannot use variable lists in WHERE statement. Use the IF statement in a data step instead. You could create a view to avoid replicating the data.

But also you cannot use more than one variable with LIKE operator.

Are you trying to exclude the whole observation if ANY of those 25 variables have that value?

You might try the WHICHC() function instead.

data no_J45 / view=no_j45 ;
  set LIB.LIB21;
  if 0 = whichc('j45', of diag_code_1-diag_code_25);
run;

PROC FREQ DATA=no_j45;
  TABLE year;
RUN;

Or perhaps INDEX() or FIND() and CATX().

  if 0 = index('j45',catx('|', of diag_code_1-diag_code_25));

Or are you trying to just exclude the individual values?

In that case you might need to transpose first.

PaigeMiller — Fri, 24 Jun 2022 15:31:58 GMT

@Reeza wrote:

PROC FREQ DATA=LIB.LIB21 (where=find((catx("|", of diag_code_1-diag_code_25), 'J45', 'it') >0));
TABLE year;
RUN;

Something like this?

I think parentheses are in the wrong spot

PROC FREQ DATA=LIB.LIB21 (where=(find(catx("|", of diag_code_1-diag_code_25), 'J45', 'it') >0));
    TABLE year;
RUN;

Ksharp — Sat, 25 Jun 2022 09:45:42 GMT

Also you could use IN operator.

data no_J45 / view=no_j45 ;
set LIB.LIB21;
array x{*} $ diag_code_1-diag_code_25 ;
if 'j45' in x ;
run;