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

[nitesh_k]

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.

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

Output: "2000-04-23"

Please help?

[shivas · Accepted solution]

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

[nitesh_k]

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

[nitesh_k]

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;