DateTime group by

[fred_major]

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

[ballardw]

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

[fred_major]

Hi

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

[ballardw]

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

Group by datepart(start) should work

[Reeza]

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.

[Patrick · Accepted solution]

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;

[fred_major]

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