Min function in proc SQL

[vomer]

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...

[art297 · Accepted solution]

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;

[Tom]

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)).

[art297]

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!

[Tom]

Thanks Art.

[Linlin]

I am the first. Congratulation Tom!  Sorry Art

[TomKari]

Woo-hoo! A SAS Master!

Does this mean you have to buy us all beer?

Congrats!

  Tom

[Tom]

Thanks.

data _null_;

  do _n_= 99 to 1 by -1;

    put _n_ 'bottles of beer on the wall';

  end;

run;

[Haikuo]

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

Haikuo

[PGStats]

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

PG

[vomer]

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!