DATA Step, Macro, Functions and more

Proc sql Question

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 91
Accepted Solution

Proc sql Question

I am trying to get the below output in  Proc SQL, instead of first. and last.variable. But not getting the desired output using min/max function.

 

Given Data   Output  
NameIdValue  NameIdValue
John10110  John10110
John20120  John20120
John30130  John30130
Kate20110  Kate20130
Kate20120  Bob10120
Kate20130  Bob20130
Bob10110     
Bob10120     
Bob20130     

 

 

 

 

Thanks for looking!


Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 9,572

Re: Proc sql Question

Posted in reply to Kalai2008

A slight variation:

proc sql;
create table want as
select
  name,
  id,
  max(value) as value
from have
group by name, id;
quit;

8

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
PROC Star
Posts: 1,312

Re: Proc sql Question

Posted in reply to Kalai2008
proc sql;
create table want as
select *
from have
group by name,id
having value=max(value);
quit;
Solution
2 weeks ago
Super User
Posts: 9,572

Re: Proc sql Question

Posted in reply to Kalai2008

A slight variation:

proc sql;
create table want as
select
  name,
  id,
  max(value) as value
from have
group by name, id;
quit;

8

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 499

Re: Proc sql Question

Posted in reply to Kalai2008

something like this. 

proc sql;
create table want  as
select name , id , max(value) as value
from have
group by 1, 2;
Frequent Contributor
Posts: 91

Re: Proc sql Question

Thank you..It worked

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 150 views
  • 0 likes
  • 4 in conversation