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

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

 

1 ACCEPTED SOLUTION
9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

Wolverine
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

PaigeMiller
Diamond | Level 26

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.

 

--
Paige Miller
Astounding
PROC Star

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.

Wolverine
Quartz | Level 8

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

ballardw
Super User

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

Wolverine
Quartz | Level 8

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.

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