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:
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: