Extract characters after a string

[shasank]

Hi Team, 

I am trying to figure out  a way to solve the problem below. I have "Type" and "Viewname" and currently trying to find a way to get the "Needed" column. 

Type	Viewname	Needed
KIND-03	prod.V_KIND-03_VISDT	VISDT
KIND-03	prod.V_KIND-03_NT_NI	NT_NI
MIND-03	prod.V_MIND-03_LID	LID
FINDLE-03	prod.V_FINDLE-03_Lab	Lab

Thank you for your time. 

Have a pleasant day!!!

[mkeintz · Accepted solution]

You didn't say so, but it appears that viewname always starts with "prod.V_" followed by type  followed by an underscore ("_"), followed by the value for needed.  If so, then concatenate "proc.V_" with type with an underscore and remove the resulting expression from viewname:

data have;
  input type :$9.  viewname :$20. ;
datalines;
KIND-03	prod.V_KIND-03_VISDT
KIND-03	prod.V_KIND-03_NT_NI
MIND-03	prod.V_MIND-03_LID
FINDLE-03	prod.V_FINDLE-03_Labrun
run;
data want;
  set have;
  *needed=transtrn(viewname,cats("prod.V_",type),'');  /*Corrected,see next line*/
  needed=transtrn(viewname,cats("prod.V_",type,'_'),'');
run;

Editted note: corrected code above.

[Patrick]

Or here some alternative syntax to achieve the same.

data want;
  set have;
  needed=substr(viewname,find(viewname,strip(type),'i')+length(type)+1);
run;

[shasank]

Thank you so much!!. This worked too. Very helpful.

[PaigeMiller]

Could you please state the rules that should be followed to extract this string? You know your data, we don't, so we'd have to guess, guessing is not a good way for us to proceed, and its hard to generalize from these four examples.