topic using distinct function in proc sql need to display all variables in SAS Programming

using distinct function in proc sql need to display all variables

sas_aspirant — Thu, 20 Jun 2024 09:52:03 GMT

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;

Re: using distinct function in proc sql need to display all variables

JosvanderVelden — Thu, 20 Jun 2024 10:37:40 GMT

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.

Re: using distinct function in proc sql need to display all variables

PaigeMiller — Thu, 20 Jun 2024 11:01:47 GMT

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?

Re: using distinct function in proc sql need to display all variables

sas_aspirant — Thu, 20 Jun 2024 11:39:14 GMT

Yeah, I got it. Thank you so much.

Re: using distinct function in proc sql need to display all variables

Tom — Thu, 20 Jun 2024 13:22:03 GMT

Why use SQL?

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