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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.