Help using Base SAS procedures

Need SAS SQL to return the minimum date as date but not as number

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Need SAS SQL to return the minimum date as date but not as number

Hi,

Need help on SAS. Check below queries:

Query 1: SELECT Publication_Date FROM test(obs=1);

Output: 2000-04-23

Query 2: SELECT MIN(Publication_Date) FROM test;

Output: 14723

NOTE: Publication_Date is of DATE type. I am using SAS 9.2 and testing above queries through JDBC layer and analyzing the resultset metadata.

Now for query 2, I need to get the output as SAS sql type Date but not as some NUMBER as in above case.

NOTE: Same query 2 in all other database vendors like oracle, MySql returns back the result in Date data type only.

I also tried below query but it doesn't solve my purpose. This query returns String but not DATE data type. Smiley Sad

SELECT put(MIN(Publication_Date),yymmdd10.) FROM test;

Output: "2000-04-23"

Please help?


Accepted Solutions
Solution
‎08-22-2012 04:42 AM
Super Contributor
Posts: 349

Re: Need SAS SQL to return the minimum date as date but not as number

Hi,

Try this...Hope this helps..

data test;

input Publication_Date:date9.;

cards;

01dec2012

01mar2012

;

run;

proc sql;

create table want as

SELECT MIN(Publication_Date) as date format=date9. FROM test;

quit;

Thanks,

Shiva

View solution in original post


All Replies
Solution
‎08-22-2012 04:42 AM
Super Contributor
Posts: 349

Re: Need SAS SQL to return the minimum date as date but not as number

Hi,

Try this...Hope this helps..

data test;

input Publication_Date:date9.;

cards;

01dec2012

01mar2012

;

run;

proc sql;

create table want as

SELECT MIN(Publication_Date) as date format=date9. FROM test;

quit;

Thanks,

Shiva

Occasional Contributor
Posts: 19

Re: Need SAS SQL to return the minimum date as date but not as number

Thanks Shiva. It did helped. I too got it from some other related posts.  Smiley Happy

Occasional Contributor
Posts: 19

Re: Need SAS SQL to return the minimum date as date but not as number

Got one solution. Thanks to "More like this" link which appears after we post the discussion.

Need to specify alias and format as below:

SELECT MIN(Publication_Date) as mindate format=Date9. FROM test;

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 4079 views
  • 3 likes
  • 2 in conversation