BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Angmar
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

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

Something like this?

PaigeMiller
Diamond | Level 26

@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;
--
Paige Miller
Tom
Super User Tom
Super User

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.

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 305 views
  • 2 likes
  • 5 in conversation