How to do in proc sql

[ganandlife]

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;

[PaigeMiller]

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.

[ganandlife]

Sorry..I am not able to try because my sas is not working at home...

[ganandlife]

CREATE TABLE SCORE AS
SELECT *
FROM NEW4
LEFT JOIN
NEW1
ON NEW4.ID = NEW1.ID;QUIT;

[ganandlife]

Not sure how to use those two nodupkey function in proc sql

[PaigeMiller]

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.

[ganandlife]

how do we create dataset new 4 and new1 to get distinct records from new and new2, then do left join in proc sql...

[Kurt_Bremser]

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.

[PaigeMiller]

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. 

[PGStats]

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;

but why?