Getting Duplicate rows

[anandmgjsa]

Hi ,

I am getting duplicate rows in data. The result is correct but it's generating same rows multiple times.

The code is :

proc sql;

create table final as

select month_year , count(distinct ccsid) as unique_visitor

from login_old

where ccsid ne '  ' 

group by month_year;

quit;

I am getting result as below :

2020-04-01    12989

2020-04-01    12989

2020-04-01    12989

2020-04-01    12989

2020-05-01    17894

2020-05-01    17894

2020-05-01    17894

2020-05-01    17894

I want only one row for each result. Duplication is not needed.

[Reeza]

Check if month_year has a format on it and that's likely causing the issue. You're seeing the dates as the same but they're not really.
You usually don't see that with the day component though, but it's the only reason I can think of for SQL to behave this way. SQL aggregation does not honour SAS formats. You need to apply the format instead.

proc sql;

create table final as

select put(t1.month_year, yymmn6.) as month_year , count(distinct ccsid) as unique_visitor

from login_old t1

where ccsid ne ' '

group by put(t1.month_year, yymmn6.);

quit;

[anandmgjsa]

It's throwing error
error : Numeric format YYMMN in put function requires a numeric argument.

[Reeza]

What's the type of your month_year variable?

[anandmgjsa]

This is character.

[Reeza]

Then that shouldn't be happening. To confirm, this is your full query and your data is exactly as shown? I would expect this for example if you had other columns included or an * somewhere but I don't see that. So it seems like SQL isn't considering your values to be the same group for some reason. It could be you have invisible characters on the date field somehow messing it up.

[Ksharp · Accepted solution]

Add 'distinct' keyword.

select DISTINCT month_year , count(distinct ccsid) as unique_visitor

[Tom]

Check the field month_year for leading spaces.

Or invisible characters like TAB ('09'x) CR ('0D'x) LF ('0A'x) non-breaking space ('A0'x) null ('00'x)

Or some of the hyphens are not hyphens but other character like en-dash or em-dash.

[Tom]

You sure you didn't include some third variable in the SELECT list of variables?

If you include non-grouping and non-aggregate values then SAS will remerge the aggregate values back on the results.

SAS will include a note in the log:

1512  proc sql;
1513  select sex, mean(age) as mean_age, height
1514  from sashelp.class
1515  group by sex
1516  ;
NOTE: The query requires remerging summary statistics back with the original data.