DATA Step, Macro, Functions and more

If statement filtering with formatted variables not working

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

If statement filtering with formatted variables not working

[ Edited ]

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;


shot.png

Accepted Solutions
Solution
‎07-21-2017 02:15 PM
Super User
Posts: 19,815

Re: problem with sas code

Posted in reply to atulsingh

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. 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: problem with sas code

Posted in reply to atulsingh

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.

 

Contributor
Posts: 38

Re: problem with sas code

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?

Solution
‎07-21-2017 02:15 PM
Super User
Posts: 19,815

Re: problem with sas code

Posted in reply to atulsingh

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. 

Super User
Posts: 5,509

Re: problem with sas code

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.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 182 views
  • 1 like
  • 4 in conversation