BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
maroulator
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

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

4 REPLIES 4
kiranv_
Rhodochrosite | Level 12

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
Tourmaline | Level 20

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
Opal | Level 21

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
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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