trimming variable

[radhikaa4]

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!

[novinosrin]

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;

[ballardw]

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.

[Tom]

---

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