## Separating observations from one column into two new ones

Solved
Frequent Contributor
Posts: 126

# Separating observations from one column into two new ones

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

Accepted Solutions
Solution
‎11-06-2017 11:17 AM
PROC Star
Posts: 509

## Re: Separating observations from one column into two new ones

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;``````

All Replies
Solution
‎11-06-2017 11:17 AM
PROC Star
Posts: 509

## Re: Separating observations from one column into two new ones

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;``````
PROC Star
Posts: 2,350

## Re: Separating observations from one column into two new ones

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.

Posts: 5,526

## Re: Separating observations from one column into two new ones

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;``````
PG
Super User
Posts: 10,778

## Re: Separating observations from one column into two new ones

``````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;``````
☑ This topic is solved.