topic Re: How to do in proc sql in SAS Programming

How to do in proc sql

ganandlife — Sun, 26 Nov 2017 12:43:50 GMT

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;

Re: How to do in proc sql

PaigeMiller — Sun, 26 Nov 2017 14:03:50 GMT

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.

Re: How to do in proc sql

ganandlife — Sun, 26 Nov 2017 14:40:50 GMT

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

Re: How to do in proc sql

ganandlife — Sun, 26 Nov 2017 14:44:53 GMT

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

Re: How to do in proc sql

ganandlife — Sun, 26 Nov 2017 14:48:45 GMT

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

Re: How to do in proc sql

PaigeMiller — Sun, 26 Nov 2017 15:20:40 GMT

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.

Re: How to do in proc sql

ganandlife — Sun, 26 Nov 2017 15:39:51 GMT

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

Re: How to do in proc sql

Kurt_Bremser — Sun, 26 Nov 2017 15:52:41 GMT

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.

Re: How to do in proc sql

PaigeMiller — Sun, 26 Nov 2017 20:02:39 GMT

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.

Re: How to do in proc sql

PGStats — Mon, 27 Nov 2017 04:54:56 GMT

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?