BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
H
Pyrite | Level 9 H
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
rogerjdeangelis
Barite | Level 11
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;


H
Pyrite | Level 9 H
Pyrite | Level 9

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.

 

H
Pyrite | Level 9 H
Pyrite | Level 9

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.

Reeza
Super User

@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. 

Reeza
Super User

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;

H
Pyrite | Level 9 H
Pyrite | Level 9

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!

H
Pyrite | Level 9 H
Pyrite | Level 9

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

 

Thank you.

Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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