topic Re: Eliminate same records in SAS Procedures

Eliminate same records

raveena — Tue, 24 May 2011 20:10:15 GMT

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

Re: Eliminate same records

SPR — Tue, 24 May 2011 21:08:49 GMT

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

Re: Eliminate same records

sbb — Tue, 24 May 2011 22:20:44 GMT

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

Re: Eliminate same records

raveena — Wed, 25 May 2011 14:43:02 GMT

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.

Re: Eliminate same records

SPR — Wed, 25 May 2011 15:37:37 GMT

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

Re: Eliminate same records

raveena — Wed, 25 May 2011 15:48:01 GMT

Thanks SPR !!

Re: Eliminate same records

Ksharp — Fri, 27 May 2011 09:51:20 GMT

[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