topic Re: Substr to extract word from last in SAS Programming

Substr to extract word from last

David_Billa — Thu, 01 Jul 2021 11:57:53 GMT

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;

Re: Substr to extract word from last

PaigeMiller — Thu, 01 Jul 2021 10:03:37 GMT

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.

Re: Substr to extract word from last

Patrick — Thu, 01 Jul 2021 10:37:00 GMT

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

Re: Substr to extract word from last

David_Billa — Thu, 01 Jul 2021 11:03:25 GMT

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

Re: Substr to extract word from last

PaigeMiller — Thu, 01 Jul 2021 11:26:42 GMT

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

Re: Substr to extract word from last

David_Billa — Thu, 01 Jul 2021 11:56:21 GMT

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

Re: Substr to extract word from last

ern_23 — Thu, 01 Jul 2021 12:17:26 GMT

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;

Re: Substr to extract word from last

Ksharp — Thu, 01 Jul 2021 12:26:59 GMT

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;