11-26-2017 07:43 AM
How to do below codes in proc sql.
proc sort data=new out=new1 nodupkey; by id; where roll=100; run; proc sort data new2 out =new4 nodupkey by id; where roll=100; run; data score; merge new4 (in=a) new1; by id; if a; run;
11-26-2017 09:03 AM
We shouldn't be writing code for you.
Give it a try in PROC SQL and if it doesn't work, show us the SASLOG and your code and I'm sure we can figure out what is wrong.
11-26-2017 10:20 AM
There is no such thing as the NODUPKEY option in PROC SQL.
If you really need that exact feature, the easiest thing is to to this in PROC SORT, where NODUPKEY exists.
11-26-2017 10:52 AM
Nodupkey in a proc sort removes all duplicates regarding the key variable only, while select distinct in sql removes duplicates where all variables in the select are identical.
So the contents of all other variables in your datasets will be critical.
It's now time that you supply some example data (use datasteps with datalines) against which we can test code.
11-26-2017 03:02 PM
It's time for you, @ganandlife, to explain why you keep asking questions where you have code that works, and you seem to insist that you have to translate it into PROC SQL. Why? It seems like a waste of time to me.
11-26-2017 11:54 PM
You can get the equivalent of nodupkey sort with SAS/SQL, or at least something close. You just need to get SQL to drop extra rows at random. For example:
proc sort data=sashelp.cars out=ncars nodupkey; by make; run; proc sql; create table ucars(drop=rnd) as select *, rand("uniform") as rnd from sashelp.cars group by make having rnd=min(rnd) order by make;