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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
Reeza
Super User

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

AllSoEasy
Obsidian | Level 7

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

Thank you!!

Reeza
Super User

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.

AllSoEasy
Obsidian | Level 7

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 2996 views
  • 3 likes
  • 2 in conversation