DATA Step, Macro, Functions and more

Separating observations from one column into two new ones

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 122
Accepted Solution

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
2 weeks ago
PROC Star
Posts: 326

Re: Separating observations from one column into two new ones

Posted in reply to maroulator

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;

View solution in original post


All Replies
Solution
2 weeks ago
PROC Star
Posts: 326

Re: Separating observations from one column into two new ones

Posted in reply to maroulator

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: 1,760

Re: Separating observations from one column into two new ones

Posted in reply to maroulator

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.

Respected Advisor
Posts: 4,930

Re: Separating observations from one column into two new ones

Posted in reply to maroulator

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,044

Re: Separating observations from one column into two new ones

Posted in reply to maroulator
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 102 views
  • 1 like
  • 5 in conversation