topic Using colon to find all numeric values beginning with a specified number in SAS Programming

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

Wolverine — Wed, 26 Apr 2017 12:43:26 GMT

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

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

RW9 — Wed, 26 Apr 2017 12:50:51 GMT

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.

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

PaigeMiller — Wed, 26 Apr 2017 12:53:38 GMT

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.

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

Astounding — Wed, 26 Apr 2017 13:17:21 GMT

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.

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

Wolverine — Wed, 26 Apr 2017 13:06:51 GMT

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.

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

RW9 — Wed, 26 Apr 2017 13:11:45 GMT

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.

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

Kurt_Bremser — Wed, 26 Apr 2017 13:16:09 GMT

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.

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

ballardw — Wed, 26 Apr 2017 14:50:24 GMT

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

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

Wolverine — Tue, 02 May 2017 12:22:22 GMT

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

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

Wolverine — Tue, 02 May 2017 12:23:49 GMT

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.