Help using Base SAS procedures

DateTime group by

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

DateTime group by


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


Accepted Solutions
Solution
‎12-05-2014 01:14 PM
Respected Advisor
Posts: 4,173

Re: DateTime group by

Posted in reply to fred_major

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;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: DateTime group by

Posted in reply to fred_major

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

Contributor
Posts: 38

Re: DateTime group by

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

Super User
Posts: 11,343

Re: DateTime group by

Posted in reply to fred_major

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

Group by datepart(start) should work

Super User
Posts: 19,772

Re: DateTime group by

Posted in reply to fred_major

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.

Solution
‎12-05-2014 01:14 PM
Respected Advisor
Posts: 4,173

Re: DateTime group by

Posted in reply to fred_major

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;

Contributor
Posts: 38

Re: DateTime group by

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 6 replies
  • 750 views
  • 0 likes
  • 4 in conversation