topic How to get col3 in the output dataset in SAS Procedures

How to get col3 in the output dataset

don21 — Mon, 13 Jul 2015 07:03:44 GMT

Hello,

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

Test

Col1 col2 col3

123 Abc

123 abc Abc

123 Abc

456. Def

456 def. def

456. Def

789 Ghi

789 ghi. 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.

Re: How to get col3 in the output dataset

AskoLötjönen — Mon, 13 Jul 2015 11:33:56 GMT

data have;

input Col1 1-3 col2 $ 6-8;

cards;

123

123 abc

123

456

456 def

456

789

789 ghi

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;

Re: How to get col3 in the output dataset

Ksharp — Mon, 13 Jul 2015 12:47:28 GMT

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;

Re: How to get col3 in the output dataset

Loko — Mon, 13 Jul 2015 13:03:31 GMT

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;

Re: How to get col3 in the output dataset

stat_sas — Mon, 13 Jul 2015 14:28:51 GMT

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;

Re: How to get col3 in the output dataset

Reeza — Mon, 13 Jul 2015 14:59:47 GMT

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;

Re: How to get col3 in the output dataset

don21 — Tue, 14 Jul 2015 03:57:12 GMT

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