Help using Base SAS procedures

How to get col3 in the output dataset

Reply
Contributor
Posts: 22

How to get col3 in the output dataset

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.

Contributor
Posts: 44

Re: How to get col3 in the output dataset

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;

Grand Advisor
Posts: 9,446

Re: How to get col3 in the output dataset

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;

Super Contributor
Posts: 305

Re: How to get col3 in the output dataset

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;

Trusted Advisor
Posts: 1,195

Re: How to get col3 in the output dataset

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;

Grand Advisor
Posts: 16,852

Re: How to get col3 in the output dataset

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;

Contributor
Posts: 22

Re: How to get col3 in the output dataset

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

Ask a Question
Discussion stats
  • 6 replies
  • 281 views
  • 8 likes
  • 6 in conversation