topic Re: Only want the last bit of a character variable in SAS Programming

Only want the last bit of a character variable

mariko5797 — Wed, 29 Dec 2021 20:30:59 GMT

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;

Re: Only want the last bit of a character variable

PaigeMiller — Wed, 29 Dec 2021 21:03:05 GMT

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.

Re: Only want the last bit of a character variable

mariko5797 — Wed, 29 Dec 2021 21:12:19 GMT

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.

Re: Only want the last bit of a character variable

PaigeMiller — Thu, 30 Dec 2021 13:08:49 GMT

@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).

Re: Only want the last bit of a character variable

ABprint — Thu, 30 Dec 2021 04:12:04 GMT

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;