BookmarkSubscribeRSS Feed
sas_aspirant
Calcite | Level 5

Hi ,

is it possible to display role variable after removing duplicates by using PROC SQL? any suggestion, Thanks in advance.

data demo;
input pid role$;
cards;
101 Hr
102 deveolper
103 analyst
101 admin

;run;
proc sql;

select distinct(pid) from demo; quit;

 

4 REPLIES 4
JosvanderVelden
SAS Super FREQ
proc sql;
	select pid, role, monotonic() as help
	from demo
	group by pid
	having help = min(help)
	;
quit;
run;

Test if you can achieve your goal with the code above. For some background read: https://communities.sas.com/t5/SAS-Communities-Library/MONOTONIC-function-in-PROC-SQL/ta-p/475752.

sas_aspirant
Calcite | Level 5
Yeah, I got it. Thank you so much.
PaigeMiller
Diamond | Level 26

This seems like an incomplete problem description.

 

Which of the two values of ROLE for PID=101 should go into the output? To ask the question in a more general way, when there are multiple values of ROLE for a given PID, which of these records should go into the output?

--
Paige Miller
Tom
Super User Tom
Super User

Why use SQL?

proc sort data=demo out=want nodupkey;
  by pid;
run;
proc print data=want;
run;

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1175 views
  • 2 likes
  • 4 in conversation