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
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;
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
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
missed that I was having a hard time reading with the way the code was shown.
Group by datepart(start) should work
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.
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;
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: