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)
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.