How to get col3 in the output dataset

[don21]

Hello,

Please help me in finding the logic for the below scenario :

Test

Col1 col2 col3

123           Abc

123  abc   Abc

123            Abc

123            Abc

456.            Def

456  def.     def

456.            Def

789              Ghi

789  ghi.      Ghi

789               Ghi

789               Ghi

If I have col1 and2 in test data set , how can I get the new data set as above? Please advise.

Many thanks in advance.

[AskoLötjönen]

data have;

input Col1 1-3 col2 $ 6-8;

cards;

123      

123  abc 

123      

123      

456      

456  def 

456      

789      

789  ghi 

789      

789      

;

run;

proc sql;

create table want as

select h1.Col1,

       h1.Col2,

       PropCase(h2.col2) as Col3

from   have h1,

       have h2

where  h1.Col1 = H2.Col1

   and h2.Col2 ne '';

quit;

[Ksharp]

Code: Program

data have;
input Col1 1-3 col2 $ 6-8;
cards;
123  
123 abc 
123  
123  
456  
456 def 
456  
789  
789 ghi 
789  
789  
;
run;
data want;
 merge have have(rename=(col2=col3) where=(col3 is not missing));
 by col1;
run;

[Loko]

Hello,

data have;
input Col1 1-3 col2 $ 6-8;
cards;
123      
123  abc 
123      
123      
456      
456  def 
456      
789      
789  ghi 
789      
789  
889   
889      
889
;
run;

data want;
set have;

if 0 then col2_=col2;

if _N_=1 then do;
declare hash h(dataset:"have(where=(missing(col2_)=0) rename=(col2=col2_))");
h.definekey('col1');
h.definedata('col2_');
h.definedone();
end;

if h.find() eq 0 then col3=col2_;

drop col2_;
run;

[stat_sas]

proc sort data=have;
by col1 descending col2;
run;

data want;
set have;
by col1;
retain col3;
if first.col1 then col3=PropCase(col2) ;
run;

[Reeza]

Since SQL MAX function works on character variables this should work as well.

proc sql;

create table want as

select a.col1, a.col2, propcase(max(col2)) as col3

from have

group by a.col1

order by a.col1, a.col2;

quit;

[don21]

Thanks everyone.. It worked.!!! :smileygrin: