DATA Step, Macro, Functions and more

substring first 7 and last two with space in between - regardless of length

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 142
Accepted Solution

substring first 7 and last two with space in between - regardless of length

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!


Accepted Solutions
Solution
‎08-09-2017 02:31 PM
Super User
Posts: 5,516

Re: substring first 7 and last two with space in between - regardless of length

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


All Replies
Super User
Posts: 11,343

Re: substring first 7 and last two with space in between - regardless of length

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.

 

Frequent Contributor
Posts: 142

Re: substring first 7 and last two with space in between - regardless of length

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;

 

 

Trusted Advisor
Posts: 1,932

Re: substring first 7 and last two with space in between - regardless of length


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.

Trusted Advisor
Posts: 1,932

Re: substring first 7 and last two with space in between - regardless of length


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?

PROC Star
Posts: 283

Re: substring first 7 and last two with space in between - regardless of length

Posted in reply to PaigeMiller

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

Solution
‎08-09-2017 02:31 PM
Super User
Posts: 5,516

Re: substring first 7 and last two with space in between - regardless of length

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 109 views
  • 0 likes
  • 5 in conversation