BookmarkSubscribeRSS Feed
ganandlife
Calcite | Level 5

 

 

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;
9 REPLIES 9
PaigeMiller
Diamond | Level 26

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
ganandlife
Calcite | Level 5
Sorry..I am not able to try because my sas is not working at home...
ganandlife
Calcite | Level 5
CREATE TABLE SCORE AS
SELECT *
FROM NEW4
LEFT JOIN
NEW1
ON NEW4.ID = NEW1.ID;QUIT;
ganandlife
Calcite | Level 5
Not sure how to use those two nodupkey function in proc sql
PaigeMiller
Diamond | Level 26

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
ganandlife
Calcite | Level 5
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
Super User

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
Diamond | Level 26

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
PGStats
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1074 views
  • 0 likes
  • 4 in conversation