Help using Base SAS procedures

Min function in proc SQL

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 117
Accepted Solution

Min function in proc SQL

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


Accepted Solutions
Solution
‎08-20-2012 03:41 PM
PROC Star
Posts: 7,468

Re: Min function in proc SQL

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;

View solution in original post


All Replies
Solution
‎08-20-2012 03:41 PM
PROC Star
Posts: 7,468

Re: Min function in proc SQL

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;

Super User
Super User
Posts: 7,039

Re: Min function in proc SQL

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

PROC Star
Posts: 7,468

Re: Min function in proc SQL

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!

Super User
Super User
Posts: 7,039

Re: Min function in proc SQL

Thanks Art.

Super Contributor
Posts: 1,636

Re: Min function in proc SQL

I am the first. Congratulation TomSmiley Happy!  Sorry ArtSmiley Wink

PROC Star
Posts: 1,167

Re: Min function in proc SQL

Woo-hoo! A SAS Master!

Does this mean you have to buy us all beer?

Congrats!

  Tom

Super User
Super User
Posts: 7,039

Re: Min function in proc SQL

Thanks.

data _null_;

  do _n_= 99 to 1 by -1;

    put _n_ 'bottles of beer on the wall';

  end;

run;

Respected Advisor
Posts: 3,156

Re: Min function in proc SQL

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

Haikuo

Respected Advisor
Posts: 4,920

Re: Min function in proc SQL

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

PG

PG
Frequent Contributor
Posts: 117

Re: Min function in proc SQL

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 Smiley Happy Keep up the good work!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 9264 views
  • 3 likes
  • 7 in conversation