topic Re: Separating observations from one column into two new ones in SAS Programming

Separating observations from one column into two new ones

maroulator — Mon, 06 Nov 2017 03:19:54 GMT

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

rateB

1.5432

rateC

2.1323

I would like to make it look like this:

Column1 Column2

rateA 1.2234

rateB 1.5432

rateC 2.1323

Re: Separating observations from one column into two new ones

kiranv_ — Mon, 06 Nov 2017 03:58:10 GMT

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;

Re: Separating observations from one column into two new ones

ChrisNZ — Mon, 06 Nov 2017 04:52:28 GMT

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.

Re: Separating observations from one column into two new ones

PGStats — Mon, 06 Nov 2017 04:55:11 GMT

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;

Re: Separating observations from one column into two new ones

Ksharp — Mon, 06 Nov 2017 12:37:02 GMT

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;