WHERE statement based on function

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 109
Accepted Solution

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: 19,167

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;

View solution in original post


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
Frequent Contributor
Posts: 109

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
Frequent Contributor
Posts: 109

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: 19,167

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.


Mine uses your 'toy dataset'

 

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: 19,167

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
Frequent Contributor
Posts: 109

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
Frequent Contributor
Posts: 109

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: 8,820

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 254 views
  • 1 like
  • 4 in conversation