Get the text before the last special character presence in SAS

[sivastat08]

Hi Team i want a new column output from below mentioned text dynamically.Kindly help on this.

Sample Data:

DATA TEST;
NEW='SBIP://METASERVER/BI SAS VA REPORTS/AR Testing [Should always be in sync with Production]/Quality Dashboard V1(Report)';
TEST1 =(TRANSLATE(scan(NEW, -1,'/'),' ','_'));

FORMAT NEW $150. TEST1 $75.;
RUN;

Out put want:

column1_want ="Quality Dashboard";

colunm2_want = "SBIP://METASERVER/BI SAS VA REPORTS/AR Testing [Should always be in sync with Production]"

[novinosrin]

Hi @sivastat08   Why is  V1(Report)  missing in your output column1_want ?

[sivastat08]

yes sir , i want (Report) should be removed from the text

i want output like this 

column1_want ="Quality Dashboard V1"

Thanks!

[novinosrin · Accepted solution]

DATA TEST;
NEW='SBIP://METASERVER/BI SAS VA REPORTS/AR Testing [Should always be in sync with Production]/Quality Dashboard V1(Report)';
FORMAT NEW $150. TEST1 $75.;
RUN;

data want;
set test;
temp=scan(new,-1,'/');
column1_want=substr(temp,1,findc(temp,'(','b')-1);
colunm2_want=substr(new,1,findc(new,'/','b')-1);
drop temp;
run;

[sivastat08]

Thank a lot Sir

[novinosrin]

---

@sivastat08 wrote:
Thank a lot Sir

---

Haven't earned that. Btw, these kind of solutions that uses scan/find/call scan have been demonstrated extensively by Guru @data_null__  in many of his posts in SAS L .

SAS-L however doesn't let users who do not have an account. What a spoil sport!!! . I don't have an account either. 

[Kurt_Bremser]

A little trickery with SAS character functions:

data have;
new = 'SBIP://METASERVER/BI SAS VA REPORTS/AR Testing [Should always be in sync with Production]/Quality Dashboard V1(Report)';
run;

data want;
set have;
index = findc(new,'/',length(new)*(-1));
column1_want = scan(substr(new,index+1),1,'(');
column2_want = substr(new,1,index-1);
drop index;
run;