BookmarkSubscribeRSS Feed
LAtwood
Calcite | Level 5
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.
13 REPLIES 13
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Doc_Duke
Rhodochrosite | Level 12
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.);
LAtwood
Calcite | Level 5
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.
LAtwood
Calcite | Level 5
The error occurs at the ")" parentheses before the seven
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
LAtwood
Calcite | Level 5
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;
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
LAtwood
Calcite | Level 5
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 ;
LAtwood
Calcite | Level 5
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
LAtwood
Calcite | Level 5
Thanks, i meant for the inputn to be input.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
And for your assignment statement, the quote marks around the INFORMAT will generate a SAS error.

Scott Barry
SBBWorks, Inc.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 3064 views
  • 0 likes
  • 3 in conversation