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.
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;
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;
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.
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.
@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 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;
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!
I went with Reeza's first solution, which works nicely given my coding context.
Thank you.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.