topic Re: Discard duplicate row which has same unique key in SAS Visual Analytics

Discard duplicate row which has same unique key

spiritgate — Wed, 02 Nov 2016 08:14:56 GMT

Hi Everyone,

Currently when I run my query, there are cases whereby there will be duplicate rows (with only one data item is different, not critical)

The below is the example (ID is the unique key):

Original Data

ID Name Date

1 joe 11/11/11

1 joe 12/11/11

2 Mary 11/11/11

3 Jane 12/12/11

3 Jane 13/12/11

Hope to achieve

ID Name Date

1 joe 11/11/11

2 Mary 11/11/11

3 Jane 12/12/11

In SAS VA data query, what should I do in order to achieve that?

Thank You

Re: Discard duplicate row which has same unique key

rcrs2 — Wed, 02 Nov 2016 14:13:51 GMT

Try this...

proc sort data=[Original Data];

by id date;

run;

data [New Data];

set [Original Data];

by id;

if first.id then unique=1;

if unique=1;

run;

Re: Discard duplicate row which has same unique key

LinusH — Thu, 03 Nov 2016 07:13:28 GMT

@rcrs2 that code is for Base SAS, not VA.

@spiritgate VA is mainly for reporting and exploration. What you are asking for is more of data transformation/scrubbing. Is it for a specific report you need to this or is it a general problem with this data set? In my mind if you have duplicates in the data it's not fit for analysis, so perhaps you need to remove the duplicates prior to loading to LASR, perhaps by using @rcrs2 program.

Re: Discard duplicate row which has same unique key

rcrs2 — Thu, 03 Nov 2016 13:42:30 GMT

Thank you for pointing it out. I didn't recognized that it was posted in VA.

Re: Discard duplicate row which has same unique key

spiritgate — Fri, 04 Nov 2016 02:55:12 GMT

@LinusH Thanks for the reply. The requirement is for specific graph. So in the data query (SAS VA), we can sniff out this kind of requirement?

Re: Discard duplicate row which has same unique key

LinusH — Fri, 04 Nov 2016 07:31:09 GMT

Data Builder is basically SQL. I don't know the nature of your data and the duplicates so I can't tell if it suits your needs.
SQL let you reduce granularity using either Group by or Select Distinct.