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
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;
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;
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.