Help using Base SAS procedures

Eliminate same records

Reply
Frequent Contributor
Posts: 90

Eliminate same records

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
Super Contributor
Super Contributor
Posts: 365

Re: Eliminate same records

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
Super Contributor
Super Contributor
Posts: 3,174

Re: Eliminate same records

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
Frequent Contributor
Posts: 90

Re: Eliminate same records

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.
Super Contributor
Super Contributor
Posts: 365

Re: Eliminate same records

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
Frequent Contributor
Posts: 90

Re: Eliminate same records

Thanks SPR !!
Super User
Posts: 9,691

Re: Eliminate same records

[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
Ask a Question
Discussion stats
  • 6 replies
  • 170 views
  • 0 likes
  • 4 in conversation