Separating observations from one column into two new ones

[maroulator]

To whom it may concern,

I would appreciate any input you may have on the following:

My data looks as follows:

Column1

rateA

1.2234

1.2234

1.2234

rateB

1.5432

1.5432

1.5432

rateC

2.1323

2.1323

2.1323

I would like to make it look like this:

Column1      Column2

rateA            1.2234

rateB            1.5432

rateC            2.1323

[kiranv_ · Accepted solution]

Something like this. 

data abc;
input Column1 $;
datalines;
rateA
1.2234
1.2234
1.2234
rateB
1.5432
1.5432
1.5432
rateC
2.1323
2.1323
2.1323
;
run;

proc sql;
create table have1 as 
select case when prxmatch('/[A-Za-z]+/', column1) then column1
end as column1_new,
case when prxmatch('/^[^A-Za-z]+$/', column1) then column1
end as column2 from abc; 

data have2;
retain column1;
set have1;
if column1_new ne '' then column1 =column1_new;
drop column1_new ;
run;

proc sql;
create table have2_final as 
select distinct * from have2
where column2 is not missing;

[ChrisNZ]

Like this?

data WANT;
  merge HAVE 
        HAVE(firstobs=2 rename=(COLUMN1=COLUMN2));
  if COLUMN1 =: 'r' ;
run;

COLUMN1	COLUMN2
rateA	1.2234
rateB	1.5432
rateC	2.1323

Change the test to suit whatever condition the first value must meet.

[PGStats]

I would do:

data temp / view=temp;
set abc;
if anyalpha(Column1) then do;
    grp + 1;
    id = 0;
    end;
id + 1;
var = cats("Column", id);
drop id;
run;

proc transpose data=temp out=want(keep=Column1 Column2);
by grp;
var Column1;
id var;
run;

[Ksharp]

data abc;
input Column1 $;
datalines;
rateA
1.2234
1.2234
1.2234
rateB
1.5432
1.5432
1.5432
rateC
2.1323
2.1323
2.1323
;
run;
data temp;
 set abc;
 length v1 $ 100;
 retain v1;
 if column1 =: 'rate' then do;v1=column1;delete;end;
run;
proc sort data=temp out=want nodupkey;
by v1;
run;