topic Re: Help getting the Max Value from a table. in SAS Programming

Help getting the Max Value from a table.

dscamelo — Thu, 14 Dec 2017 17:18:31 GMT

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.

Re: Help getting the Max Value from a table.

ballardw — Thu, 14 Dec 2017 17:32:10 GMT

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 &target;
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.

Re: Help getting the Max Value from a table.

dscamelo — Thu, 14 Dec 2017 17:42:39 GMT

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.

Re: Help getting the Max Value from a table.

ballardw — Thu, 14 Dec 2017 18:03:33 GMT

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.