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 |
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
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
Great! Thanks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.