BookmarkSubscribeRSS Feed
raveena
Obsidian | Level 7
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
6 REPLIES 6
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Obsidian | Level 7
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
Quartz | Level 8 SPR
Quartz | Level 8
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
Obsidian | Level 7
Thanks SPR !!
Ksharp
Super User
[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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 953 views
  • 0 likes
  • 4 in conversation