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