topic Min function in proc SQL in SAS Procedures

Min function in proc SQL

vomer — Mon, 20 Aug 2012 19:28:09 GMT

Hi Guys,

I am noticing some strange row duplication when using the following min query (in bold) :

proc sql;

create table TEST as

select ID,

min(FIRST_Date, information_date) as First_V2,

max(LAST_Date)as Last_V2,

from base

group by ID;

quit;

Is this wrong syntax? It looks like sas is not getting the min value for information_date...

Re: Min function in proc SQL

art297 — Mon, 20 Aug 2012 19:41:02 GMT

There shouldn't be a comma at the end of the max(etc. etc) line and, if you don't want duplicates, add a distinct statement. e.g.:

data base;

informat first_date information_date last_date date9.;

input id first_date information_date last_date;

cards;

1 29apr2012 30apr2012 15aug2012

1 30apr2012 29apr2012 14aug2012

2 29apr2012 30apr2012 15aug2012

3 30apr2012 29apr2012 14aug2012

;

proc sql;

create table TEST as

select DISTINCT ID,

min(min(FIRST_Date, information_date)) as First_V2 format=date.,

max(LAST_Date)as Last_V2 format=date9.

from base

group by ID;

quit;

Re: Min function in proc SQL

Tom — Mon, 20 Aug 2012 19:43:45 GMT

You seem to have confused the MIN aggregation syntax of SQL with the MIN() function syntax of SAS.

You asked it to set First_V2 to the minimum of two variables on the current observation and LAST_V2 to the maximum of LAST_Date over the whole dataset. If you want to instead find the aggregate min over all the variables then use min(min(a,b)) or min(min(a),min(b)).

Re: Min function in proc SQL

art297 — Mon, 20 Aug 2012 19:56:54 GMT

First, you were absolutely correct, thus I've added the min of min to my original post. But, more importantly, would you please respond to this post so that I can be the first to congratulate you on attaining the extremely well deserved master status. I can't believe it has taken this long as you've been a SAS Master well before this forum was created!

Re: Min function in proc SQL

Tom — Mon, 20 Aug 2012 19:58:56 GMT

Thanks Art.

Re: Min function in proc SQL

Linlin — Mon, 20 Aug 2012 20:00:20 GMT

I am the first. Congratulation Tom! Sorry Art

Re: Min function in proc SQL

TomKari — Mon, 20 Aug 2012 20:20:19 GMT

Woo-hoo! A SAS Master!

Does this mean you have to buy us all beer?

Congrats!

Tom

Re: Min function in proc SQL

PGStats — Mon, 20 Aug 2012 20:44:42 GMT

More than deserved! Congrats Tom! Now it's official.

Re: Min function in proc SQL

vomer — Mon, 20 Aug 2012 22:11:47 GMT

Thanks Art & Tom. This explanation helps me a lot! As I suspected it was a syntax error.

Also congrats to Tom! you guys are so talented and helpful Keep up the good work!

Re: Min function in proc SQL

Tom — Tue, 21 Aug 2012 03:20:30 GMT

Thanks.

data _null_;

do _n_= 99 to 1 by -1;

put _n_ 'bottles of beer on the wall';

end;

run;

Re: Min function in proc SQL

Haikuo — Tue, 21 Aug 2012 03:29:43 GMT

Tom, LOL, and many many congrats! I wish it was Friday night so I could really use some of your beers.

Haikuo