topic proc sql question - min function in SAS Procedures

proc sql question - min function

AllSoEasy — Tue, 01 Oct 2013 17:15:54 GMT

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!

Re: proc sql question - min function

Reeza — Tue, 01 Oct 2013 17:34:07 GMT

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

Re: proc sql question - min function

AllSoEasy — Tue, 01 Oct 2013 19:04:51 GMT

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

Thank you!!

Re: proc sql question - min function

Reeza — Tue, 01 Oct 2013 20:13:24 GMT

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

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.

Re: proc sql question - min function

AllSoEasy — Tue, 01 Oct 2013 20:17:14 GMT

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!