DATA Step, Macro, Functions and more

Using colon to find all numeric values beginning with a specified number

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

Using colon to find all numeric values beginning with a specified number

[ Edited ]

I'm using SAS 9.4.  I'm trying to collapse approximately 1000 codes into 77 groups.  In the syntax below, codes such as 170, 171, 172, etc from the UB_92_REV_CD_GP_1 variable should all go in the "170 TEST" group of the variable REV_CHAR_GP_1.

 

The UB_92_REV_CD_GP_1 variable is 4-digit numeric, while REV_CHAR_GP_1 is a character variable with a length of 65.

 

I've tried several variations (one at a time, not all at once), but I can't get it to recode any value starting with 17 into the group "170 TEST".  I can only get it to work if I specify each value within the 170 range.  Isn't the colon supposed to find any value beginning with the specified number?

 

/*THESE VARIATIONS WORK ONLY FOR 170, NOT 171, 172, ETC*/
IF UB_92_REV_CD_GP_1 in:("017","170") THEN REV_CHAR_GP_1 = "170 TEST";
IF UB_92_REV_CD_GP_1 =:"170" THEN REV_CHAR_GP_1 = "170 TEST";


/*THESE VARIATIONS DO NOT WORK*/
IF UB_92_REV_CD_GP_1 in:("017","17") THEN REV_CHAR_GP_1 = "170 TEST";
IF UB_92_REV_CD_GP_1 in:("17","017") THEN REV_CHAR_GP_1 = "170 TEST";
IF UB_92_REV_CD_GP_1 in:('017','17') THEN REV_CHAR_GP_1 = "170 TEST";
IF UB_92_REV_CD_GP_1 in:(017,17) THEN REV_CHAR_GP_1 = "170 TEST";
IF UB_92_REV_CD_GP_1 =:"17" THEN REV_CHAR_GP_1 = "170 TEST";  

 


Accepted Solutions
Solution
‎05-02-2017 08:20 AM
Super User
Posts: 6,963

Re: Using colon to find all numeric values beginning with a specified number

if substr(left(put(UB_92_REV_CD_GP_1,4.)),1,2) = "17" then REV_CHAR_GP_1 = "170 TEST";

assuming that UB_92_REV_CD_GP_1 is in fact numeric.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,413

Re: Using colon to find all numeric values beginning with a specified number

Its always a good idea to post test data in the form of a datastep and what you want the output to look like.  There is likely a far better method.

As to your question:

IF UB_92_REV_CD_GP_1 in:("017","170") THEN REV_CHAR_GP_1 = "170 TEST";

None of the text 171, 172 etc. start with either 017 or 170, hence why it does not work.  017171 does start with 017 and 170171 starts with 170.

 

What you may be better of doing is taking the number part out of the string and using that, something like (and this is where test data/output comes in handy as just guessing):

data want;
  set have;
  if input(scan(your_data,1," "),best.) between 170 and 179 then group="Group 170";
run;

Using the right datatype will make your problem much simpler. 

Contributor
Posts: 38

Re: Using colon to find all numeric values beginning with a specified number

As I said, I tried several variations.  I understand why

IF UB_92_REV_CD_GP_1 in:("017","170") THEN REV_CHAR_GP_1 = "170 TEST";

doesn't work, but why doesn't

IF UB_92_REV_CD_GP_1 in:("017","17") THEN REV_CHAR_GP_1 = "170 TEST";

work?  Values such as 170, 171, 172, etc all start with 17.

 

 

By "doesn't work", I mean that when I run frequencies on REV_CHAR_GP_1, the group 170 TEST does not appear in the output, even though I know that UB_92_REV_CD_GP_1 contains values such as 170, 171, 172, etc.  There are no errors or warnings in the log.

 

Unfortunately

IF UB_92_REV_CD_GP_1 in:("17","017", " 17") THEN REV_CHAR_GP_1 = "170 TEST";

does not work either.

Super User
Super User
Posts: 7,413

Re: Using colon to find all numeric values beginning with a specified number

Post test data in the form of a datastep!

 

Wiothout seeing the data we can't say, maybe there is a space or an unprintable value in your string, maybe your using the wrong data who knows.  This is why we ask for test data so we can see it as well. 

Trusted Advisor
Posts: 1,630

Re: Using colon to find all numeric values beginning with a specified number

[ Edited ]

What do you mean by "do not work"? What happens? Error in SASLOG, or wrong result? Show us the error in the SASLOG or explain to us the wrong result and show us (examples of) the incoming data and the wrong result.

 

Super User
Posts: 5,092

Re: Using colon to find all numeric values beginning with a specified number

[ Edited ]

Of all those that you have listed here, the first two in the "do not work" section actually work.  The only exception might be if your variable could begin with a leading blank.  You might have to add to the list:

 

IF UB_92_REV_CD_GP_1 in:("17","017", " 17") THEN REV_CHAR_GP_1 = "170 TEST";

 

If that doesn't work (and it appears from  your note that it doesn't), take a look at the data.  Take a subset of the observations (from those that you think should be selected), and try something along these lines:

 

data _null_;

set subset_with_17;

testvar = '*' || UB_92_REV_CD_GP_1 || '*';

put testvar testvar $hex20.;

run;

 

Let's find out what is really in there.

Solution
‎05-02-2017 08:20 AM
Super User
Posts: 6,963

Re: Using colon to find all numeric values beginning with a specified number

if substr(left(put(UB_92_REV_CD_GP_1,4.)),1,2) = "17" then REV_CHAR_GP_1 = "170 TEST";

assuming that UB_92_REV_CD_GP_1 is in fact numeric.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 38

Re: Using colon to find all numeric values beginning with a specified number

This works and was easy to incorporate into my existing syntax.  Thanks!

Super User
Posts: 10,538

Re: Using colon to find all numeric values beginning with a specified number


Wolverine wrote:

I'm using SAS 9.4.  I'm trying to collapse approximately 1000 codes into 77 groups

Looks like a very likely candidate for a custom format, and with codes that look like that a MULTILABEL might even be of help

 

Since: "UB_92_REV_CD_GP_1 variable is 4-digit numeric"

a numeric format would let you use a range:

Proc format;

value somefmtname

170-179 = 'Text I want associated with these variable values'

;

Then use the format with the variable in procedures to get the desired group and text label. No extra varaibles needed. And you don't get into the ugly "numerics that start with character" stuff.

Contributor
Posts: 38

Re: Using colon to find all numeric values beginning with a specified number

The frequency output is getting sent to an Excel output file, and unfortunately the assigned formats don't survive that process.  That's why I'm recoding into a string variable.

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 180 views
  • 0 likes
  • 6 in conversation