topic Re: Need to replace last character in a variable with a numeric number in SAS Procedures

Need to replace last character in a variable with a numeric number

LAtwood — Wed, 16 Sep 2009 13:03:39 GMT

I have a character variable titled "load_lbs" and it is formatted as an example like this: "079460K"

The letter will always fall in 7th (or last position).

I need to replace the last character value with a number and it needs to be a numeric variable.

K = -1, L=-2, A=1, etc.
For example the K needs to be replaced with the number 1 and the number needs to be negative. (Ebcdic)

This is already a SAS dataset that needs to be modified

Any help with this would be appreciated.

Re: Need to replace last character in a variable with a numeric number

sbb — Wed, 16 Sep 2009 13:37:28 GMT

One approach is to use the TRANWRD function to perform the replacement and also use the FIND function to locate the character match, using argument three as -99 to start from the end. Two ARRAYs of candidate source and replacement character strings (one or more than one) would be declared in your program and you would use a DO / END code paragraph to loop through the list of character-strings for replacement.

Scott Barry
SBBWorks, Inc.

Re: Need to replace last character in a variable with a numeric number

Doc_Duke — Wed, 16 Sep 2009 13:48:02 GMT

So, does the result for
079460K
look like
079460-1
or
-0794601

Either way, you can do it with a SELECT statement with a SUBSTR to pull of the last letter and the various WHEN statements to do the assignments, again using SUBSTR.

If you do the replacement in place, the result will still be a character string containing numerals. To get a new variable that is numeric, you need to wrap the SUBSTR with an INPUT function.

The SELECT would look like
SELECT (SUBSTR(load_lbs,7,1));

For 079460K and the a numeric output, WHEN would be

WHEN ('K') NewLoadLbs= - INPUT(substr(load_lbs,1,6)||'1'),7.);

Re: Need to replace last character in a variable with a numeric number

LAtwood — Wed, 16 Sep 2009 14:08:25 GMT

Thanks for you help so far, but
I have multiple when statements:
and I get the following error when I try this:
when ('{') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'0'),7.),
-
388
76
-
200
ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

ERROR 200-322: The symbol is not recognized and will be ignored.

Re: Need to replace last character in a variable with a numeric number

LAtwood — Wed, 16 Sep 2009 14:09:17 GMT

The error occurs at the ")" parentheses before the seven

Re: Need to replace last character in a variable with a numeric number

sbb — Wed, 16 Sep 2009 14:20:51 GMT

With SAS, it's important to share more than just the (apparent) failing code line -- share the ENTIRE DATA step code for most accurate diagnosis and feedback.

And, for self-diagnosis, try commenting out parts of the DATA step code to attempt to isolate the problem root cause -- also add PUTLOG commands and such to help with your own code diagnosis.

Scott Barry
SBBWorks, Inc.

Re: Need to replace last character in a variable with a numeric number

LAtwood — Wed, 16 Sep 2009 14:23:20 GMT

Here is the line of code:
data FD020_0148_LKUP ;
set FD020_0148_LKUP ;
select (substr(wgh_reg_lbs,7,1));
when ('{') new_wgh_reg_lbs= substr(wgh_reg_lbs,1,6)||'0',8.),
when ('A') new_wgh_reg_lbs= substr(wgh_reg_lbs,1,6)||'1',8.);
/*when ('B') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'2'),7.),
when ('C') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'3'),7.),
when ('D') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'4'),7.),
when ('E') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'5'),7.),
when ('F') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'6'),7.),
when ('G') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'7'),7.),
when ('H') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'8'),7.),
when ('I') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'9'),7.),
when ('}') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'0'),7.),
when ('J') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'1'),7.),
when ('K') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'2'),7.),
when ('L') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'3'),7.),
when ('M') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'4'),7.),
when ('N') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'5'),7.),
when ('O') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'6'),7.),
when ('P') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'7'),7.),
when ('Q') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'8'),7.),
when ('R') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'9'),7.);*/
end;

Re: Need to replace last character in a variable with a numeric number

sbb — Wed, 16 Sep 2009 14:29:09 GMT

Check your assignment statements in the first WHEN stmt as compared to those that following the "A" WHEN test - notice any difference?

BTW, I tested your code by pasting it into a SAS window and converted the DATA and SET statements to be:

data _null_ ;
retain wgh_reg_lbs '222222A';
select (substr(wgh_reg_lbs,7,1));

After adding the statements below at the end- the code was self-contained and could be tested independent of all other processing -- which helped reveal the problem cause - your INPUT function is missing in the first WHEN:

PUTLOG _ALL_;
RUN;

Some thoughts for future self-diagnosis and program testing.

Scott Barry
SBBWorks, Inc.

Re: Need to replace last character in a variable with a numeric number

LAtwood — Wed, 16 Sep 2009 14:35:39 GMT

Scott
I apologize. I was trying different options when the first two lines of code to try to de-bug the error. Here is what the code looks like and I am still getting the same error:
*------------------------------------------------------transform data--;
data FD020_0148_LKUP ;
set FD020_0148_LKUP ;
select (substr(wgh_reg_lbs,7,1));
when ('{') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'0'),8.),
/* when ('A') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'1'),8.),
when ('B') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'2'),7.),
when ('C') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'3'),7.),
when ('D') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'4'),7.),
when ('E') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'5'),7.),
when ('F') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'6'),7.),
when ('G') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'7'),7.),
when ('H') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'8'),7.),
when ('I') new_wgh_reg_lbs= INPUT(substr(wgh_reg_lbs,1,6)||'9'),7.),
when ('}') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'0'),7.),
when ('J') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'1'),7.),
when ('K') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'2'),7.),
when ('L') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'3'),7.),
when ('M') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'4'),7.),
when ('N') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'5'),7.),
when ('O') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'6'),7.),
when ('P') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'7'),7.),
when ('Q') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'8'),7.),
when ('R') new_wgh_reg_lbs= -INPUT(substr(wgh_reg_lbs,1,6)||'9'),7.);*/
end;
date =inputn(dt,'yymmdd.') ;
drop dt;
drop load_mis_lbs;
drop load_skids;
drop load_reg_lbs;
drop wgh_mis_lbs;
drop other_lbs;
drop wgh_reg_lbs;
run ;

Re: Need to replace last character in a variable with a numeric number

LAtwood — Wed, 16 Sep 2009 14:43:10 GMT

Scott,

I have found the issue.
data FD020_0148_LKUP ;
set FD020_0148_LKUP ;
select (substr(wgh_reg_lbs,7,1));
when ('{') new_wgh_reg_lbs= INPUT((substr(wgh_reg_lbs,1,6)||'0'),8.) ;

A parantheses needed to be added before the substr statement in the when clause. I was also missing the otherwise;

thanks for your help

Re: Need to replace last character in a variable with a numeric number

sbb — Wed, 16 Sep 2009 15:14:53 GMT

Those parantheses bracketing the SUBSTR function and concatenation have no code/processing impact - though they may be meaningful for reading/viewing the code.

Scott Barry
SBBWorks, Inc.

Re: Need to replace last character in a variable with a numeric number

sbb — Wed, 16 Sep 2009 15:18:45 GMT

Also, you may want to code an OTHERWISE statement, in case your SELECT/WHEN/END does not have a suitable match to fulfill one of the WHEN statements listed. Also, I don't believe that your INPUTN function use is appropriate.

Scott Barry
SBBWorks, Inc.

Re: Need to replace last character in a variable with a numeric number

LAtwood — Wed, 16 Sep 2009 15:31:23 GMT

Thanks, i meant for the inputn to be input.

Re: Need to replace last character in a variable with a numeric number

sbb — Wed, 16 Sep 2009 15:35:05 GMT

And for your assignment statement, the quote marks around the INFORMAT will generate a SAS error.

Scott Barry
SBBWorks, Inc.