BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fred_major
Calcite | Level 5


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

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

6 REPLIES 6
ballardw
Super User

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
Calcite | Level 5

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
Super User

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

Group by datepart(start) should work

Reeza
Super User

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
Opal | Level 21

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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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