DATA Step, Macro, Functions and more

Help getting the Max Value from a table.

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Help getting the Max Value from a table.

Hi.

 

I need some help getting the max value from a column in a table. I initially tried writing a function as such:

 

 

PROC FCMP OUTLIB=work.func.test;
	FUNCTION get_max(table $, column $);
		PROC SQL;
			SELECT MAX(column) INTO :ret FROM table;
		QUIT;
		RETURN ret;
	ENDSUB;
RUN;

 

 

But it doesn't work. Any ideas?

 

Thanks in advance.


Accepted Solutions
Solution
‎12-14-2017 02:32 PM
Super User
Posts: 13,046

Re: Help getting the Max Value from a table.

That use is just a subquery

proc sql;
   create table example as
   select name, sex, age
   from sashelp.class
   where age= (select max(age) from sashelp.class)
   ;
quit;

as long as that select returns a single value no problems.

 

View solution in original post


All Replies
Super User
Posts: 13,046

Re: Help getting the Max Value from a table.

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of a dataset, the actual results and the expected results. Data should be in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

Can you demonstrate exactly how you wanted to use that result? I am very sure you can't call proc sql in the middle of a data step or other Sql call. From the documentation: PROC FCMP enables you to write functions and CALL routines using DATA step syntax. You aren't using data set syntax so the creation probably fails.

 

You can place a value into a global macro variable with something like this:

%macro max(table, column, target);
%global ⌖
PROC SQL;
   SELECT MAX(&column) INTO : &target FROM &table;
QUIT;

%mend;

%max(sashelp.class,age,val);
%put &val;

but that type of macro "function" can't be called in many places because of the Proc SQL portion.

 

Occasional Contributor
Posts: 8

Re: Help getting the Max Value from a table.

Hi ballardw, thanks for your reply.

 

Being more specific, it doesn't work because, as you pointed out, PROC FCMP can't deal with PROC SQL commands inside it. The snippet I offered was meant to convey the idea of what I need.

 

I need a simple function that can return the max value from a column in a table. I meant to use it as something like:

 

PROC SQL;
	CREATE TABLE work.op1 AS
	SELECT
		*
	FROM
		work.input1
	WHERE
		date_updated = get_max(other_table, date_updated);
QUIT;

assuming the get_max from original post had worked.

 

Your macro suggestion is something that had occured to me, but I mean to write a function and store it so everyone in my work group could use, rather than everyone having to include and run macros that use global variables.

 

I hope I was clear with what I need.

 

Solution
‎12-14-2017 02:32 PM
Super User
Posts: 13,046

Re: Help getting the Max Value from a table.

That use is just a subquery

proc sql;
   create table example as
   select name, sex, age
   from sashelp.class
   where age= (select max(age) from sashelp.class)
   ;
quit;

as long as that select returns a single value no problems.

 

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 145 views
  • 2 likes
  • 2 in conversation