The following query returns a count of events by day. event_posting_date sample value: 05/03/11
If I run the query in SQL Server, I get the proper number of records for each day, but when I run the query in SAS EG using proc sql, I get duplicates. Specifically, if the count field returns 1, I get one row, if the count field returns 2, I get 2 identical rows, if the count field returns 3, I get 3 identical rows.
I can throw a distinct on the query to eliminate this, but I'd rather understand why this is happening and address properly.
Thanks
select
input(event_posting_date,MMDDYY8.) as day,
count(cybs_exception_id) as count
from frs.staging_records
group by
input(event_posting_date,MMDDYY8.)
;
day count
18570 1
18764 2
18764 2
18767 1
18779 3
18779 3
18779 3
is event_posting_date in datetime format?
try:
select
int(input(event_posting_date,MMDDYY8.)) as day,
count(cybs_exception_id) as count
from frs.staging_records
group by
int(input(event_posting_date,MMDDYY8.))
;
event_posting_date is a varchar(28) in the following format: 05/03/11 (MM/DD/YY)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.