Grouping & use of Max

[sasbanker]

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"
AcctID	Name	Location	clrRed	clrBlue	clrGreen
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
Desired Output:  TestOUT
AcctID	Name	Location	clrRed	clrBlue	clrGreen
100	Bob	MA	Red	Blue
200	John	NC	Red		Green
300	Frank	VA	Red	Blue	Green

[Ksharp · Accepted solution]

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

[sasbanker]

Great!  Thanks.