10-01-2013 01:15 PM
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:
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
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!
10-01-2013 04:13 PM
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
NOTE: Table WORK.WANT created, with 19 rows and 6 columns.
10-01-2013 04:17 PM
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!
Need further help from the community? Please ask a new question.