Eliminate same records

[raveena]

Hi,

In table that I have 2 columns provno and liceno as below,

NO Prov Lice
1 100675 100897
2 100989 100989
3 100897 100897
4 100976 100972

In above table the row 1 and 3 has same records; in that case I need to get the report to only identify those cases in which the numbers are different?

The reports needs to look like,

NO Prov Lice
1 100675 100897
2 100976 100972

Can you please me on code to get this report.

Thanks

[SPR]

Hello Raveena,

This is a solution:
[pre]
data i;
retain n;
input NO Prov Lice;
if Prov NE Lice;
n + 1;
drop NO;
rename n=NO;
datalines;
1 100675 100897
2 100989 100989
3 100897 100897
4 100976 100972
run;
[/pre]
Sincerely,
SPR

[sbb]

Look at using PROC SORT NODUPKEY and specify the SAS variable(s) of importance (for uniqueness) in the BY statement.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search arguments, this topic / post:

proc sort nodupkey site:sas.com

remove duplicate records site:sas.com Message was edited by: sbb

[raveena]

Hi SPR,

Thanks, it works. I need one more concern,

Provno Liceno
110987-A85 110987
117866-B85 117866
11347 34567
002478-A19 002479

My report needs to look like as below,

Provno Liceno
11347 34567
002478-A19 002479

In above example, need to get only the records which are different(only 3 and 4 row) and the 1 and 2 row considered as same so it should be eliminate from the report. The reason is that the first 6 numerics match the numerics contained in the liceno field. In essence 119966 matches 119966.

Please let me know .

Thanks.

[SPR]

This is a possible solution:
[pre]
data i;
retain no;
input Provno $ 1-10 Liceno $ 12-17;
if SUBSTR(Provno,1,6) NE Liceno;
no + 1;
datalines;
110987-A85 110987
117866-B85 117866
11347 34567
002478-A19 002479
run;
[/pre]
SPR

[raveena]

Thanks SPR !!

[Ksharp]

[pre]
data i;
input Provno $ 1-10 Liceno $ 12-17;
if Provno NE: strip(Liceno);
datalines;
110987-A85 110987
117866-B85 117866
11347 34567
002478-A19 002479
run;

[/pre]

Ksharp