07-03-2013 09:55 AM
07-03-2013 10:32 AM
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:
create table want (drop=no) as
Select Distinct Actlevel,Fee,Height,weight , monotonic() as no From SASUSER.ADMIT
group by Actlevel
Ksharp: that is being said, what if you have multiple max height? And isn't the bedtime for you? lol.
07-03-2013 10:49 AM
Sure. It is time to go to sleep.
or try this one :
Select Distinct Actlevel,Fee,Height,weight From x.ADMIT
group by Actlevel
07-03-2013 11:17 AM
You made my day with simple solution.thanks a lot.....!!Seems it better to use NODUPKEY in Proc Sort instead of trying this.
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:
04-17-2017 02:52 PM
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:
input name$ age;
Can we separate all the unique records for eg sa 2, an 7,ka 7, ka 8,am 3,am 2 an 3.
10-13-2016 12:40 PM
07-02-2013 02:57 PM
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;
(4) proc freq data=DSN noprint;
tables variable/out=unique (Keep=variable count where=(count=1));
(5) Proc sort data=DSN out=temp;
by variable ; Run;
if not first.variable and last.variable;
07-03-2013 05:23 AM
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,
P.S I have a feeling, key= option/unique in set statement can perhaps be used for remving duplicates on a self merge?
07-03-2013 05:40 AM
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;
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.
Proc sort data=DSN ;
Data Unique Duplicates;
If First.Var1 then Output Unique;
else output Duplicates;
Hope this helps.
Need further help from the community? Please ask a new question.