BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

Hi!

 

Trying to substring a character string, but can quite get the last two characters in the value to be correct:

 

Here is the syntax I used...only output 2nd from the last?

providcallsign=substr(providcallsign,1,7)||''||substr(providcallsign,length(providcallsign),-2)

 

Here is what my data looks like:

 

HAVE WANT
DUSTOFF DUSTOFF
DUSTOFF 05 DUSTOFF 05
DUSTOFF 05 D005 DUSTOFF 05
DUSTOFF DUSTOFF 05 DUSTOFF 05
DUSTOFF D005 DUSTOFF 05
DUSTOFF05 DUSTOFF 05

 

 

help is appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Two pieces to fix ...

 

First, don't you want a blank in between?  Make sure there is a blank between the quotes:  ||' '||

 

Second, there is an extra comma before -2 at the end of your expression.  -2 is part of the calculation of the starting point for SUBSTR.  Technically, it looks like you actually should be using -1 instead of -2.  But you'll soon find that out.

View solution in original post

6 REPLIES 6
ballardw
Super User

You will have to clarify or expand on your rule for the two records with only "DUSTOFF". How, from the general content of the data, do we know that the second "DUSTOFF" is supposed to be DUSTOFF 05 when there is no "05" as part of the data?

 

Do you have any other callsigns other than those that start with "DUSTOFF"? Do any of them have a desired value other than "05" as the second part? More examples may be needed. The bare dustoff may pose a challenge but what I have done to clean up some such data is to create custom formats. The process would be to run proc freq on your callsign variable to get a list you can copy from and then create a format similar to:

 

proc format library=work;
value callsign
"DUSTOFF 05" ,"DUSTOFF 05 D005", "DUSTOFF D005", "DUSTOFF05"    ="DUSTOFF 05"
;
run;

data want;
   set have;
   Providcallsign= put(callsign,$Callsign.);
run;

Note that I did not include just plain DUSTOFF as there appears to be some variation in the outcome and we really need clarification.

 

jenim514
Pyrite | Level 9

Sorry for all the confusion.  I basically want to keep the first seven characters and the last two characters regardless of what is in between (e.g. another 'DUSTOFF' ).  I suppose it there are no additional characters past 'DUSTOFF' (such as in the first value in the example I sent)...I can start with: if length(variable)<7 then do;

 

 

PaigeMiller
Diamond | Level 26

@jenim514 wrote:

Sorry for all the confusion.  I basically want to keep the first seven characters and the last two characters regardless of what is in between (e.g. another 'DUSTOFF' ). 


This was completely clear from your original statement.

 

What was not clear was what was happening, and why it was giving you results you didn't want, and what the mysterious phrase "only output 2nd from the last?" means. Still not clear what that means.

 

As you can see, you'd be much better off showing us your data step (all of it, not just one line), representative data to illustrate the problem (you did that), and whatever results you get, where you can point out what isn't working properly. By doing this (all of this), you will get quick answers. Leaving some of it out gets you lots of questions.

--
Paige Miller
PaigeMiller
Diamond | Level 26

@jenim514 wrote:

Hi!

 

Trying to substring a character string, but can quite get the last two characters in the value to be correct:

 

Here is the syntax I used...only output 2nd from the last?

providcallsign=substr(providcallsign,1,7)||''||substr(providcallsign,length(providcallsign),-2)

 

Here is what my data looks like:

 

HAVE WANT
DUSTOFF DUSTOFF
DUSTOFF 05 DUSTOFF 05
DUSTOFF 05 D005 DUSTOFF 05
DUSTOFF DUSTOFF 05 DUSTOFF 05
DUSTOFF D005 DUSTOFF 05
DUSTOFF05 DUSTOFF 05

 

 

help is appreciated!


Just to help us all out, and to help you get faster answers in the future, I would like to point out that an incomplete and fragmentary sentence which is not at all clear is not a good thing when you are asking questions.

 

What does

 

only output 2nd from the last?

 

mean?

--
Paige Miller
novinosrin
Tourmaline | Level 20

@jenim514 like others , i am not completely sound with your requirement. But I do find the question a bit funny though-

 


data want;
set have;
if var='DUSTOFF' then new_var=var;
else if length(var)>7 and index(var,'DUSTOFF')>0 then new_var='DUSTOFF 05';
run;

Astounding
PROC Star

Two pieces to fix ...

 

First, don't you want a blank in between?  Make sure there is a blank between the quotes:  ||' '||

 

Second, there is an extra comma before -2 at the end of your expression.  -2 is part of the calculation of the starting point for SUBSTR.  Technically, it looks like you actually should be using -1 instead of -2.  But you'll soon find that out.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 824 views
  • 0 likes
  • 5 in conversation