topic Re: trimming variable in SAS Programming

trimming variable

radhikaa4 — Fri, 13 Dec 2019 14:29:03 GMT

I want to just pick out the middle instance of "have_id"

have_id	want_id
1234-ABCD-5678	ABCD
6564-DGFD-4958	DGFD

Currently I used

proc sql;

select have_id, right(left(subjectID, 9), 4) as want_id

from testdata; quit;

it works in my sql server but not in the SAS. any help would be great!

Re: trimming variable

novinosrin — Fri, 13 Dec 2019 14:33:06 GMT

Hi @radhikaa4

data have;
input have_id $30.;
cards;	
1234-ABCD-5678	
6564-DGFD-4958	
;

data want;
set have;
want_id=scan(have_id,median(1,countw(have_id,'-')),'-');
run;

Re: trimming variable

ballardw — Fri, 13 Dec 2019 19:29:15 GMT

If your values are all as simple as you display then

scan(have_id,2,'-') as want_id

should work.

This assumes that you always have at least 1 hyphen character and that the part you want is bit immediately after the first hyphen. The hyphen in the scan is telling the SCAN function to use only the hyphen as a word delimiter so other characters such as space, * , slash , period, question mark and most punctuation would be ignored for determining word boundaries. If you have other characters that need to be considered then a more comprehensive example should be provided.

Re: trimming variable

Tom — Fri, 13 Dec 2019 19:47:34 GMT

@radhikaa4 wrote:

I want to just pick out the middle instance of "have_id"

have_id want_id

1234-ABCD-5678 ABCD

6564-DGFD-4958 DGFD

Currently I used

proc sql;

select have_id, right(left(subjectID, 9), 4) as want_id

from testdata; quit;

it works in my sql server but not in the SAS. any help would be great!

LEFT() and RIGHT() do not take any numeric arguments, just the string that want to left align or right align.

Why wouldn't you just use the SUBSTR() function in either SQL server or SAS?

substr(subjectid,6,4)