If statement filtering with formatted variables not working

[atulsingh]

I am trying to run the below code but the values in variable (binary_sales) are missing. Please refer the attached screenshot for stat_globalsales dataset.

data binary_globalsale;
set stat_globalsale;
if Sales = '-Average' then binary_Sales = 0;
if Sales = '+Average' then binary_Sales = 1;
run;
proc print data=binary_globalsale;
run;

[ballardw]

Please verify that your Sales variable is actually a string. A picture may be showing a formated value and we can't tell.

If they are character did you check to see if your Sales values have leading spaces?

There is also a possibilit of a different charcter than the + and - from your keyboard in a character variable that looks similar.

You might examine the results of

data examine;
   set stat_globalsale (keep=sales); 
   y = rank('-');
   x = rank(substr(sales,1,1));
run;

to see if the values of x and y match where sales should be '-Average'. If they don't then you have different leading character(s). If X=32 there's a leading space.

[atulsingh]

The thing is, earlier i have formated the Sales column using proc format such that the values which were above 2600 were labelled as '+Average' and the values below 2600 were rated as '-Average'.

Now i want the above values as:

'-Average' should become = 0 and 

'+Average' should become = 1.

What to do?

[Reeza · Accepted solution]

---

@atulsingh wrote:

The thing is, earlier i have formated the Sales column using proc format such that the values which were above 2600 were labelled as '+Average' and the values below 2600 were rated as '-Average'.

Now i want the above values as:

'-Average' should become = 0 and 

'+Average' should become = 1.

What to do?

---

You need to use the underlying unformatted values or convert them using PUT in your recoding statements.

Re: your subject line "problem with sas code". You have another one today? Please use descriptive subjects in the future. We answer these questions not just to help you but any future users who have similar problems and choose to search for answers. 

[Astounding]

Implied by Reeza's comment, this would be a way:

if Sales > 2600 then binary_Sales = 1;

else binary_sales = 0;

You should refer to the actual value in your variable, not the formatted value.

Also, be careful to properly assign BINARY_SALES when SALES is negative, zero, or missing, or exactly 2600.  You have to at least consider the possibilities.