topic Re: DateTime group by in SAS Procedures

DateTime group by

fred_major — Fri, 05 Dec 2014 17:12:08 GMT

Hi,

I have some data where the date is datetime, I am attempting to do a datepart so as to group by.

Please see below.

proc sql exec;
connect to sqlservr as Sstats (server= xxxxx database=Source_B);
create table test as select date,count(status)as Totals

from connection to sstats(
SELECT datepart(start)format weekdate17. as date,status

FROM [Table]
group by start)
where (START)>= &fromdate
and (START) < &todate ;
disconnect from sStats;

quit; run;

Help please.

Fred

Re: DateTime group by

ballardw — Fri, 05 Dec 2014 17:50:10 GMT

I don't see a summary function so you likely want to use ORDER BY instead of GROUP BY.

ORDER BY datepart(start)

should get you there instead of GROUP BY Start

Re: DateTime group by

fred_major — Fri, 05 Dec 2014 17:56:25 GMT

It is actually reads count(status)as Totals

I need it to give me the total count by date, i'm not clear on how to do this when field is datetime.

Fred

Re: DateTime group by

ballardw — Fri, 05 Dec 2014 18:05:56 GMT

missed that I was having a hard time reading with the way the code was shown.

Group by datepart(start) should work

Re: DateTime group by

Reeza — Fri, 05 Dec 2014 18:06:48 GMT

Using datepart() to convert to a date in SAS SQL is one way.

You're using pass through code though, so your code needs to be SQL compliant not SAS compliant, so the datepart function possibly won't work the same on the server. Find the appropriate function on SQL, most likely a variant on the convert() function.

Re: DateTime group by

Patrick — Fri, 05 Dec 2014 18:14:55 GMT

You haven't posted valid SQL code. You are using SAS syntax like datepart() in the code section for explicit SQL Server code - so this can't work. You're then having a "group by" in this section as well but the summary function "count(status) as Totals " is outside on the SAS side. This can't work.

It's may be easier for you to first write your SQL query in SAS syntax (using implicit SQL) and only when this works and you need to improve performance, amend to code to run as explicit SQL.

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

libname mydata <definitions for SQL db > ;

proc sql exec;

create table test as

select

datepart(start) as date format=weekdate17. ,

count(status)as Totals

FROM mydata.[Table]

where (START)>= &fromdate and (START) < &todate /* assumes macro vars contain datetime values, else use datepart() as well */

group by datepart(start)

;

quit;

Re: DateTime group by

fred_major — Fri, 05 Dec 2014 18:35:22 GMT

Hi,

Yes sorry I understand that my code was all over the place, I attempted all different things in an effort before submitting my post.

I took your advice and it was what i needed, thank you.

Worked perfect..

Fred