Inequality signs issue

[pmpradhan]

My data is as follows:

ID [num var]           Percent [Character var]
1452                      10
1254                      20.00
4852                      30
7526                      40
7542                      0
7530                      50.00
9856                      50
3256                      51
7854                     100
7128                      500
1287                      80
4568                      5.00
7540                      8.00
9530                      9
9920                      25

I'm trying to create another dataset from the above data where the percent is larger than 0 and less than or equal to 50.

I used 

proc sql;
create table x as
select id, percent from test where "0"<percent<="50";
quit;

but it gives me the observation that has 100 percent.

Please help!

[Astounding · Accepted solution]

As you have seen, SAS compares character strings differently.  Try it this way:

where 0 < input(percent, 8.) <= 50

[pmpradhan]

Thank you but I got this error when implementing "where 0 < input(percent, 8.) <= 50 " ERROR: Syntax error while parsing WHERE clause.

[Astounding]

If you really want help with this, post the log.  It should be relatively tiny, and the solution should be relatively simple.  "Syntax error" doesn't say enough.

[pmpradhan]

Thank you so much! this worked!

[novinosrin]

remove the quotes in  "0"<percent<="50";  and try again. I believe the auto conversion should take effect 

[pmpradhan]

Tried it didn't work. 😞

[novinosrin]

ok sorry,no worries as you have @Astounding converted solution. 

[pmpradhan]

Well that didn't work as well.

[novinosrin]

please post your log 

data have;
do var='1','2','3','4','5';
output;
end;
run;
proc sql;
create table want as
select *
from have
where 2<input(var,8.)<=4;
quit;

[pmpradhan]

Yes, this worked! Thank you! 🙂

[TomKari]

Not tested, but I don't believe that comparison format works in a SQL query. Try

"0" < percent and percent <="50"

Also, if your "percent" variable is character, it is VERY likely you'll get unexpected results from a character comparison. Test carefully! If "percent" is numeric, remove the double quotes from 0 and 50.

Tom

[pmpradhan]

Tried but this also gives me the observation that has percent =100.