## WHERE statement based on function

Solved
Frequent Contributor
Posts: 122

# WHERE statement based on function

[ Edited ]

Hello,

I am excited to get some input.  I have a two part (sequential) question.

Part 1: I would like to run a PROC PRINT which has a WHERE statement based on a function, see following:

``````PROC PRINT DATA=SAS_Comm;
WHERE MAX value for X;
RUN;``````

Part 2: I would like to create a "%LET", kind of based on the PROC PRINT result. I get that the answer to this can probably be better derived from a different procedure, if so, I would happlily use that (e.g., PROC MEANS instead of PROC PRINT), though I still need the PROC PRINT part in to work as a quality assurance measure in my coding. So what I am looking for is, I have two variables X and Y. So I want the value for Y for the Max X. And in particular, I want that Y value to be inserted into   '%LET Z = 98;.

``````data SAS_Comm;
input X Y;
CARDS;
1	4
2	9
9	98
5	2
0	0
6	8
2	7
;``````

So all together the final code would be a functional form of the following:

``````DATA SAS_Comm;
INPUT X Y;
CARDS;
1	4
2	9
9	98
5	2
0	0
6	8
2	7
;
PROC PRINT DATA=SAS_Comm;
WHERE MAX value for X;
RUN;
%LET Z = 98; ``````

P.S., Let us assume that there is only one unique Y associated with each X. Thus, the code won't kick out a row with X = 9; Y=98 and say X = 10; Y=78. Let me know if I need to clarify any part of this post.

Accepted Solutions
Solution
‎12-28-2016 09:01 AM
Super User
Posts: 23,776

## Re: WHERE statement based on function

[ Edited ]

Solution 1:

1. Use PROC SQL to display value and create macro variable.

Proc SQL;

select y into :yMaxz

from sasComm

having x=max(x);

quit;

2. Check value stored

%put &yMaxz;

3. Use in WHERE

Proc PRINT data=sasComm;

where y=&yMaxz;

run;

You could probably achieve all this from a single SQL query or a proper sort.

Solution 2:

Proc SQL;

Select x, y

from sascomm

having x=max(x);

quit;

Solution 3:

Proc SORT data=sascomm;

by descending X;

run;

*largest x is now first record in the dataset;

proc PRINT data=sascomm(obs=1);

run;

All Replies
Valued Guide
Posts: 505

## Re: WHERE statement based on function

``````This might give you a start. from SAS-L

/* T1000890 Data step where clause "set sashelp.class(where=(age < avgVal('age')))"

Data step where clause "set sashelp.class(where=(age < avgVal('age')))"

NOT RECOMMENDED - DUE TO RERPEATED SQL CODE - BUT INTERESTING
SLOW DO TO RESTRICTIONS ON HOW SAS COMPILES FCMP RUN MACRO?

HAVE
====

Up to 40 obs from sashelp.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

1    Alfred      M      14     69.0      112.5
2    Alice       F      13     56.5       84.0
3    Barbara     F      13     65.3       98.0
4    Carol       F      14     62.8      102.5
5    Henry       M      14     63.5      102.5
6    James       M      12     57.3       83.0
7    Jane        F      12     59.8       84.5
8    Janet       F      15     62.5      112.5
9    Jeffrey     M      13     62.5       84.0
...
18     Thomas      M      11     57.5       85.0
19     William     M      15     66.5      112.0

WANT ( Records where Age is less than the average of age(13.3))
=========================================================

Up to 40 obs from WORK.CLS total obs=10

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

1    Alice       F      13     56.5       84.0  mean age=13.3
2    Barbara     F      13     65.3       98.0
3    James       M      12     57.3       83.0
4    Jane        F      12     59.8       84.5
5    Jeffrey     M      13     62.5       84.0
6    John        M      12     59.0       99.5
7    Joyce       F      11     51.3       50.5
8    Louise      F      12     56.3       77.0
9    Robert      M      12     64.8      128.0
10    Thomas      M      11     57.5       85.0

WORKING SOLUTION
================

set sashelp.class(where=(age < avgVal('age')));

FULL SOLUTION
=============

%macro testmacro;
proc sql; select mean(&age) into :ret
from sashelp.class;quit
%mend testmacro;

proc fcmp outlib = sasuser.ds.functions;
function avgVal(age \$);
call symputx('age',age);
rc = run_macro('testmacro', ret);
if rc eq 0 then return(ret);
else return(.);
endsub;
run;

/* Make a call from the DATA step. */
option cmplib = (sasuser.ds);

%symdel age ret;;
data cls;
set sashelp.class(where=(age < avgVal('age')));
run;

``````
Frequent Contributor
Posts: 122

## Re: WHERE statement based on function

Thanks for the quick input. I am in the process of reviewing it. Though, I wanted to mention that I am absolutely terrible with MACROS and fairly inexperienced with PROC SQL.

Frequent Contributor
Posts: 122

## Re: WHERE statement based on function

[ Edited ]

Well, I believe I will need someone to actually use my toy dataset, in that I appear inept in this  area. I blindly jammed my vars into the above code and got a very long data set to kick out what I would imagine code be created in a single PROC SQL command. And I still have no idea how to link this to a '%Let' statement???

I am going to need someone to hold my hand throughout this post, since as I mentioned I am walking around with no compass or relevant knowledge!

Thanks you.

Super User
Posts: 23,776

## Re: WHERE statement based on function

H wrote:

Well, I believe I will need someone to actually use my toy dataset, in that I appear inept in this  area. I blindly jammed my vars into the above code and got a very long data set to kick out what I would imagine code be created in a single PROC SQL command. And I still have no idea how to link this to a '%Let' statement???

I am going to need someone to hold my hand throughout this post, since as I mentioned I am walking around with not compass or relevant knowledge!

Thanks you.

You don't need a LET. In fact, you don't want one - because it would be manually set. The first proc SQL has an INTO which creates a macro variable with the value of 98. Run it. Let me know specifically what the issue is. Note that my post includes 3 different solutions to achieve for desired results. You only need one, pick the one you understand.

Solution
‎12-28-2016 09:01 AM
Super User
Posts: 23,776

## Re: WHERE statement based on function

[ Edited ]

Solution 1:

1. Use PROC SQL to display value and create macro variable.

Proc SQL;

select y into :yMaxz

from sasComm

having x=max(x);

quit;

2. Check value stored

%put &yMaxz;

3. Use in WHERE

Proc PRINT data=sasComm;

where y=&yMaxz;

run;

You could probably achieve all this from a single SQL query or a proper sort.

Solution 2:

Proc SQL;

Select x, y

from sascomm

having x=max(x);

quit;

Solution 3:

Proc SORT data=sascomm;

by descending X;

run;

*largest x is now first record in the dataset;

proc PRINT data=sascomm(obs=1);

run;

Frequent Contributor
Posts: 122

## Re: WHERE statement based on function

Thank you Roger and Reeza.

Reeza, I am on my way out the door, but your reponse appears to work and is exactly what I was desiring. GRAND in deed! I will mark the post solved tomorrow morning after I verify that it works in my actual context and that I have no more questions.

Much appreciation!

Frequent Contributor
Posts: 122

## Re: WHERE statement based on function

I went with Reeza's first solution, which works nicely given my  coding context.

Thank you.

SAS Super FREQ
Posts: 9,371

## Re: WHERE statement based on function

Hi:
Don't be afraid of the SAS Macro facility. It is really very cool once you understand it.
cynthia
https://support.sas.com/resources/papers/proceedings13/120-2013.pdf
☑ This topic is solved.