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

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

 

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

View solution in original post

12 REPLIES 12
Astounding
PROC Star

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

 

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

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

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
Quartz | Level 8
Thank you so much! this worked!
novinosrin
Tourmaline | Level 20

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

pmpradhan
Quartz | Level 8
Tried it didn't work. 😞
novinosrin
Tourmaline | Level 20

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

pmpradhan
Quartz | Level 8
Well that didn't work as well.
novinosrin
Tourmaline | Level 20

 

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
Quartz | Level 8
Yes, this worked! Thank you! 🙂
TomKari
Onyx | Level 15

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
Quartz | Level 8
Tried but this also gives me the observation that has percent =100.

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
  • 12 replies
  • 1354 views
  • 2 likes
  • 4 in conversation