BookmarkSubscribeRSS Feed
spiritgate
Fluorite | Level 6

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

5 REPLIES 5
rcrs2
Calcite | Level 5

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;

 

LinusH
Tourmaline | Level 20
@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.
Data never sleeps
rcrs2
Calcite | Level 5

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

spiritgate
Fluorite | Level 6

@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?

LinusH
Tourmaline | Level 20
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.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1621 views
  • 0 likes
  • 3 in conversation