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

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

nitesh_k — Wed, 22 Aug 2012 08:31:50 GMT

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 — Wed, 22 Aug 2012 08:42:33 GMT

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 — Wed, 22 Aug 2012 08:44:21 GMT

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;

nitesh_k — Wed, 22 Aug 2012 08:45:46 GMT

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