Substr to extract word from last

[David_Billa]

In the following program I want to extract the word from last.Desired result is MIN, MAX, AVG

I'm looking for two Solutions here. One with using only substr and other with combination of Scan and substr or with any function.

Any help?

data test;
length CRE $50;
input CRE $;
datalines;
[NDID]#MIN2
[NDID_TST]#MAX2
[NDID_PROD]#AVG
;
run;

[PaigeMiller]

want = scan(cre,-1,'#');

To do this with SUBSTR, you'd probably need to use the FIND function, find the location of the # character, and then SUBSTR. SCAN is so much easier.

[Patrick]

@David_Billa Below for you to pick and chose

data have;
  length CRE $50;
  input CRE $;
  datalines;
"[NDID]#MIN2"
"[NDID_TST]#MAX2"
"[NDID_PROD]#AVG"
"[NDID_PROD]#XXX"
;

data want;
  set have;
  length word1_l4 $4 word1_l3 word2-word4 $3;
  word1_l4=scan(cre,-2,'#"');
  word1_l3=scan(cre,-2,'#"');
  word2=compress(scan(cre,-2,'#"'),,'d');
  word3=substrn(scan(cre,-2,'#"'),1,3);
  word4=substrn(cre,find(cre,'#')+1,3);
run;

[David_Billa]

@Patrick  what is the meaning of this Argument '#"' in your code?

[PaigeMiller]

---

@David_Billa wrote:

In the following program I want to extract the word from last.Desired result is MIN, MAX, AVG

I'm looking for two Solutions here. One with using only substr and other with combination of Scan and substr or with any function.

Any help?

data test;
length CRE $50;
input CRE $;
datalines;
"[NDID]#MIN2"
"[NDID_TST]#MAX2"
"[NDID_PROD]#AVG"
;
run;

---

Looking at the above code, is it truly trying to say that the first and last character of the values of CRE strings are double quotes? Or are the double quotes a mistake, and you really mean

data test;
length CRE $50;
input CRE $;
datalines;
[NDID]#MIN2
[NDID_TST]#MAX2
[NDID_PROD]#AVG
;
run;

[David_Billa]

@PaigeMiller @Patrick sorry, double quotes a mistake, Ignore the double quotes in the provided data.

[ern_23]

Assuming you have only one # in the column and extract the info without quotation marks, below code should work out.

DATA TEST_OUTPUT;
SET TEST;

DESIREDCOLUMN = COMPRESS(SUBSTR(CRE,INDEX(CRE,"#")+1),"""");
RUN;

[Ksharp · Accepted solution]

data test;
length CRE $50;
input CRE $;
datalines;
[NDID]#MIN2
[NDID_TST]#MAX2
[NDID_PROD]#AVG
;
run;

data want;
  set test;
  want=scan(cre,-1,,'ka');
run;