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.
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;
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;
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;
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;
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;
Thanks everyone.. It worked.!!! :smileygrin:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.