How to do in proc sql

Reply
Occasional Contributor
Posts: 9

How to do in proc sql

 

 

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;
Respected Advisor
Posts: 2,794

Re: How to do in proc sql

Posted in reply to ganandlife

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.

--
Paige Miller
Occasional Contributor
Posts: 9

Re: How to do in proc sql

Posted in reply to PaigeMiller
Sorry..I am not able to try because my sas is not working at home...
Occasional Contributor
Posts: 9

Re: How to do in proc sql

Posted in reply to ganandlife
CREATE TABLE SCORE AS
SELECT *
FROM NEW4
LEFT JOIN
NEW1
ON NEW4.ID = NEW1.ID;QUIT;
Occasional Contributor
Posts: 9

Re: How to do in proc sql

Posted in reply to ganandlife
Not sure how to use those two nodupkey function in proc sql
Respected Advisor
Posts: 2,794

Re: How to do in proc sql

Posted in reply to ganandlife

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.

--
Paige Miller
Occasional Contributor
Posts: 9

Re: How to do in proc sql

Posted in reply to PaigeMiller
how do we create dataset new 4 and new1 to get distinct records from new and new2, then do left join in proc sql...
Super User
Posts: 9,855

Re: How to do in proc sql

Posted in reply to ganandlife

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 2,794

Re: How to do in proc sql

Posted in reply to ganandlife

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. 

--
Paige Miller
Esteemed Advisor
Posts: 5,474

Re: How to do in proc sql

Posted in reply to ganandlife

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?

 

PG
Ask a Question
Discussion stats
  • 9 replies
  • 273 views
  • 0 likes
  • 4 in conversation