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

I have the following column

 

data have;
 length visit $20.;
 input visit $22.;
 cards;
	INITIAL SCREENING
	MOCK CHMI DAY 1
	MOCK CHMI DAY 2
	MOCK CHMI DAY 3
	CHMI 1 DAY 1
	CHMI 1 DAY 5
	CHMI 1 DAY 15
	CHMI 2 DAY 1
	CHMI 2 DAY 5
	CHMI 2 DAY 20
	CHMI 2 DAY 27
 ;
run;

 

I would like to create a column like such out of the first column

 

data want;
 length day $8.;
 input day $10.;
 cards;
	SCRN
	DAY 1
	DAY 2
	DAY 3
	DAY 1
	DAY 5
	DAY 15
	DAY 1
	DAY 5
	DAY 20
	DAY 27
 ;
run;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

@mariko5797 wrote:

It was just a part of the specifications I was given, but I also need a column with just the number. So I'll take that too and just concatenate 'Day' to it later.


Well you might want to ask if getting a numeric 15 is actually better than a character string. It is hard to imagine a situation where character DAY 15 would be superior to numeric 15. You might want to see if this can be changed to improve the process.

 

But specs are specs. So here is my solution:

 

data want;
    set have;
    length day $ 22;
    day=scan(visit,-1);
    if day='SCREENING' then day='SCRN';
    else day=cat('DAY ',day);
run;

I note that if some sorting (or creating tables) of data set WANT is needed, then DAY 15 sorts ahead of DAY 2, and usually people don't want that. Numeric 15 always sorts after numeric 2 (assuming your sort is ascending).

--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

May I ask why you want "DAY 15" instead of just "15" or a numeric 15? That seems like whatever you do next, a numeric 15 would be easier to work with.

--
Paige Miller
mariko5797
Pyrite | Level 9

It was just a part of the specifications I was given, but I also need a column with just the number. So I'll take that too and just concatenate 'Day' to it later.

PaigeMiller
Diamond | Level 26

@mariko5797 wrote:

It was just a part of the specifications I was given, but I also need a column with just the number. So I'll take that too and just concatenate 'Day' to it later.


Well you might want to ask if getting a numeric 15 is actually better than a character string. It is hard to imagine a situation where character DAY 15 would be superior to numeric 15. You might want to see if this can be changed to improve the process.

 

But specs are specs. So here is my solution:

 

data want;
    set have;
    length day $ 22;
    day=scan(visit,-1);
    if day='SCREENING' then day='SCRN';
    else day=cat('DAY ',day);
run;

I note that if some sorting (or creating tables) of data set WANT is needed, then DAY 15 sorts ahead of DAY 2, and usually people don't want that. Numeric 15 always sorts after numeric 2 (assuming your sort is ascending).

--
Paige Miller
ABprint
Fluorite | Level 6

Here's another way of doing it with Proc SQL. Functions findw() and length() to derive start and end positions in substrn(), then compress() to keep only numbers, since you'll have "DAY (some number)" after substrbn() output. 

 

proc sql;
create table want as 
select   visit
	,case when visit like '%SCREENING%' then 'SCRN'
	 else catx(" ","DAY",compress(substrn(visit,findw(visit, 'DAY'),length(visit)),'','A')) 
	 end as day length=8 
from have
;
quit;

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 518 views
  • 1 like
  • 3 in conversation