Hi Ksharp.
Ahhh...Still can't able to get desired output.tried with your updated code ,yet producing 9 observations instead of 3.
Here am attaching ADMIT dataset for your ref.
Regards.
Sanjeev.K
OK.
Proc Sql;
Select Distinct Actlevel,Fee,Height,weight From x.ADMIT
group by Actlevel
having height=max(height);
quit;
I don't think you do can it in one SQL pass without using this undocumented monotonic(), as this is the only unique index that SQL can use to product the nodup results, all of the other efforts of generating certain unique feature based on your data will NOT be as robust:
Proc Sql;
create table want (drop=no) as
Select Distinct Actlevel,Fee,Height,weight , monotonic() as no From SASUSER.ADMIT
group by Actlevel
having no=max(no);
quit;
Haikuo
Ksharp: that is being said, what if you have multiple max height? And isn't the bedtime for you? lol.
HaiKuo.
Sure. It is time to go to sleep.
OP,
or try this one :
Proc Sql;
Select Distinct Actlevel,Fee,Height,weight From x.ADMIT
group by Actlevel
having cats(fee,height,weight)=max(cats(fee,height,weight));
quit;
You made my day with simple solution.thanks a lot.....!!Seems it better to use NODUPKEY in Proc Sort instead of trying this.
Hi
Sorry for testing your patients 🙂 and finally you came with the your mark answer....!!!!
Your Solution also working perfectly...
And Sorry for both of you ,because i can not able to make your answer as Correct Answer as i am not the person who created this Discussion..:-) :smileysilly:
Thanks &Regards.
Sanjeev.K
Hi Everyone!
I had one qsn regarding first.and last. Can we separate duplicates of 2 variables with first. and last. .We can remove duplicates for 1 varialble but not sure if we can separate duplicates for 2 variables. For eg:
In this program:
data a;
input name$ age;
datalines;
am 2
am 3
am 3
am 3
an 3
an 7
ka 7
ka 7
ka 8
sa 2
;
run;
Can we separate all the unique records for eg sa 2, an 7,ka 7, ka 8,am 3,am 2 an 3.
Please help
Thanks!
Hello
Hi Andy,
I may not be much familiar with all ways but trying my best.....
(1) proc sort data=DSN noduprecs;
by _all_ ; Run;
(2) Proc sort data=DSN out=sample nodupkey dupout=Duplicate;
by var1; Run;
(3) Proc Sql noprint;
create table unique as select distinct (*) from DSN;
quit;
(4) proc freq data=DSN noprint;
tables variable/out=unique (Keep=variable count where=(count=1));
Run;
(5) Proc sort data=DSN out=temp;
by variable ; Run;
Data Unique;
set temp;
by variable;
if not first.variable and last.variable;
run;
thanx uma...
Is there any way to remove duplicateS with the help of PROC APPEND and MERG as well?????
Anand, to use merge & append, you need to sort the observation & while sorting Nodup key take cares of dup records!!!
(5) is wrong.
Should be:
(5) Proc sort data=DSN out=temp;
by variable ; Run;
Data Unique;
set temp;
by variable;
if first.variable;
run;
PG
Absolutely
Hi Ksharp,
I would like to know different ways of identifying duplicates or in other words subsetting just the duplicates into a new dataset, perhaps I should have posted a new question however I thought I would add here as I noticed you and PG Sir got involved in the thread, got me excited, also others can learn too.
Cheers from England,
Charlotte
P.S I have a feeling, key= option/unique in set statement can perhaps be used for remving duplicates on a self merge?
You want to create a new dataset with all duplicates observations ???
If yes, here is the way..
Proc sort data=DSN out=sample nodupkey dupout=Duplicate;
by var1;
Run;
A new dataset duplicate will be created and it contains all duplicate observations from DSN dataset.
You can achive the same by using Data step as well.
like
Proc sort data=DSN ;
by var1;
Run;
Data Unique Duplicates;
set DSN;
by Var1;
If First.Var1 then Output Unique;
else output Duplicates;
run;
Hope this helps.
Regards.
Sanjeev.K
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.