Help using Base SAS procedures

proc sql question - min function

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 105
Accepted Solution

proc sql question - min function

Okay,

So this question isn't quite "how to do something within proc sql" but more about how proc sql implements the min() function --

Essentially, I have this piece of SAS code:

proc sql;

  create table day_t1_path_t as

  select Acct as acct_t1, CurBal as curbal_t1, Date as date_t1, min(date) as path_start_t1, 2 as tenure_t1,

  min((select curbal_t from day_t_path_t where acct_t = Acct),curbal) as minbal_t1

  from small_init;

quit;

Now what I am trying to do is port this code over to be able to run in SQL management studio with raw SQL (no sas functions) -- I know that the Min() function works differently in SAS than SQL, for example to be able to implement the second min() call in SQL I need to use the case statement because SQL's min function doesn't allow for variable comparisons, only minimums across columns (i.e: "case when (select curbal_t from day_t_path_t where acct_t = Acct) < curbal then (select curbal_t from day_t_path_t where acct_t = Acct) else CURBAL end) as minbal_t1")

However, the problem I'm having is with the first call to min() "min(date) as path_start_t1"  -- I am trying to take the minimum value for "date" in the "from" table, and use that value for observations of this "path_start_t1" variable in my select clause. This way the new table I am creating will have a column "path_start_t1" where it's value in each row is equal to the minimum date in the "from" table. -- This works perfectly in SAS, but in SQL I get an error for all of the variables in the select clause other than the min(date) because they aren't in a group by (or aggregate function) -- this makes sense because in SQL the min(function) is pulling one single value and so must collapse all other variables in the select clause by groupings in the select clause so that it is clear to SQL how to determine the return value for min() in each row of the output table. What I would like to understand is how this works in SAS - is the under lying function implementing some extra SQL queries to make this possible, or is this not at all possible in SQL and done with some other internal SAS functions?

I hope my question is clear and applicable to this forum. I'd appreciate any information, thank you very much!


Accepted Solutions
Solution
‎10-01-2013 01:34 PM
Super User
Posts: 19,792

Re: proc sql question - min function

Posted in reply to AllSoEasy

Do it in a subquery cross joined in?

select a.*, b.path_start_t1

from small_init

cross join (select min(date) as path_start_t1 from small_init) b

View solution in original post


All Replies
Solution
‎10-01-2013 01:34 PM
Super User
Posts: 19,792

Re: proc sql question - min function

Posted in reply to AllSoEasy

Do it in a subquery cross joined in?

select a.*, b.path_start_t1

from small_init

cross join (select min(date) as path_start_t1 from small_init) b

Frequent Contributor
Posts: 105

Re: proc sql question - min function

Ah ha yes it is that simple! Very interesting that SAS internally does this work automatically.

Thank you!!

Super User
Posts: 19,792

Re: proc sql question - min function

Posted in reply to AllSoEasy

It does do it automatically but then tells you it does it, so the same amount of "work" behind the scenes, but less up front for the programmer perhaps Smiley Happy

2671  proc sql;

2672  create table want as

2673  select *, min(age) as min_age

2674  from sashelp.class;

NOTE: The query requires remerging summary statistics back with the original

      data.

NOTE: Table WORK.WANT created, with 19 rows and 6 columns.

Frequent Contributor
Posts: 105

Re: proc sql question - min function

Wow I didn't even notice that, I never thought that the Log would reveal that kind of information, that's super interesting! Definitely does make things a little more straight-forward for the user/programmer -- in that context the intended result is clear; if only SQL studio performed that kind of functionality wrapping behind the scenes!

Thanks Reeza!

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 878 views
  • 3 likes
  • 2 in conversation