Help using Base SAS procedures

Grouping & use of Max

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Grouping & use of Max

I have a source table that looks like the following "TestIN".

I want to read the file and create the output shown in TestOUT.

I assume it requires grouping and use of the Max function, but I don't know how to write the code.

I'm a newbie with no formal training, and any help will be appreciated.

Source Input File:  "TestIN"
AcctIDNameLocationclrRedclrBlueclrGreen
100BobMARed
100BobMABlue
200JohnNCRed
200JohnNCGreen
300FrankVARed
300FrankVABlue
300FrankVAGreen
Desired Output:  TestOUT
AcctIDNameLocationclrRedclrBlueclrGreen
100BobMARedBlue
200JohnNCRedGreen
300FrankVARedBlueGreen

Accepted Solutions
Solution
‎05-15-2015 10:27 AM
Super User
Posts: 10,020

Re: Grouping & use of Max

Posted in reply to sasbanker

Two way. which one you like better ?

data have;
input AcctID     Name $     Location $     clrRed $     clrBlue     $ clrGreen $;
cards;
100     Bob     MA     Red     . .
100     Bob     MA     .     Blue     .
200     John     NC     Red     . .     
200     John     NC          . .     Green
300     Frank     VA     Red     . .
300     Frank     VA     .     Blue     .
300     Frank     VA     .     .     Green
;
run;

proc sql;
create table want1 as
 select AcctID,Name,Location,max(clrRed) as clrRed,max(clrBlue) as clrBlue,max(clrGreen) as clrGreen
  from have
   group by AcctID,Name,Location;
quit;

data want2;
 update have(obs=0) have;
 by AcctID Name Location;
run;

Xia Keshan

View solution in original post


All Replies
Solution
‎05-15-2015 10:27 AM
Super User
Posts: 10,020

Re: Grouping & use of Max

Posted in reply to sasbanker

Two way. which one you like better ?

data have;
input AcctID     Name $     Location $     clrRed $     clrBlue     $ clrGreen $;
cards;
100     Bob     MA     Red     . .
100     Bob     MA     .     Blue     .
200     John     NC     Red     . .     
200     John     NC          . .     Green
300     Frank     VA     Red     . .
300     Frank     VA     .     Blue     .
300     Frank     VA     .     .     Green
;
run;

proc sql;
create table want1 as
 select AcctID,Name,Location,max(clrRed) as clrRed,max(clrBlue) as clrBlue,max(clrGreen) as clrGreen
  from have
   group by AcctID,Name,Location;
quit;

data want2;
 update have(obs=0) have;
 by AcctID Name Location;
run;

Xia Keshan

Occasional Contributor
Posts: 9

Re: Grouping & use of Max

Great!  Thanks.

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 182 views
  • 0 likes
  • 2 in conversation