topic Getting Duplicate rows in SAS Programming

Getting Duplicate rows

anandmgjsa — Thu, 06 May 2021 15:31:27 GMT

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-05-01 17894

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

Re: Getting Duplicate rows

Reeza — Thu, 06 May 2021 15:48:15 GMT

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;

Re: Getting Duplicate rows

anandmgjsa — Thu, 06 May 2021 16:16:18 GMT

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

Re: Getting Duplicate rows

Reeza — Thu, 06 May 2021 17:02:36 GMT

What's the type of your month_year variable?

Re: Getting Duplicate rows

anandmgjsa — Thu, 06 May 2021 17:52:39 GMT

This is character.

Re: Getting Duplicate rows

Reeza — Thu, 06 May 2021 18:09:51 GMT

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.

Re: Getting Duplicate rows

Ksharp — Fri, 07 May 2021 13:28:13 GMT

Add 'distinct' keyword.

select DISTINCT month_year , count(distinct ccsid) as unique_visitor

Re: Getting Duplicate rows

Tom — Fri, 07 May 2021 14:30:31 GMT

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.

Re: Getting Duplicate rows

Tom — Fri, 07 May 2021 14:36:24 GMT

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.