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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
ballardw
Super User

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.

 

dscamelo
Obsidian | Level 7

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.

 

ballardw
Super User

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.

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 4731 views
  • 2 likes
  • 2 in conversation