SQL to SAS Code

[Smitha9]

I have the code in SQL. But  its not working in SAS procsql. Is there anyway that I could work it in SAS proc SQL?

select *

when code like '43[0-8]%' then 'A'

when code like '582.%' then 'B'

when code like '58[56]%' then 'C'

from table

;

quit;

I want this is SAS. I am not getting the result of %

[Reeza]

What SQL type is this? DB2, Oracle?

[Amir]

Hi,

Thanks for supplying what you have done.

What do you mean by "its not working in SAS procsql"? If you are getting an error message then please post the log showing the code with any messages using the Insert Code icon "</>". I have assumed you have started with "proc sql;".

If there are no errors or warnings, etc. in the log then show us sample input data and respective output data you expect vs what you get. Ideally, these data sets can be provided in the form of data steps reading in data via datalines, for example.

Thanks & kind regards,

Amir.

Edit: Typo.

[ballardw]

While SAS does have a Select/when coding it is in a data step.

Are you trying to assign values to a variable in the result? That would be a CASE statement

Which might look like

proc sql;
select * ,
case 
   when code like '43[0-8]%' then 'A'
   when code like '582.%' then 'B'
   when code like '58[56]%' then 'C'
   else ' ' 
end AS NEWVAR
from table
;
quit;

You would have to have comma after the * in the select to separate it from the following CASE.

I have included a result of a single blank when none of the conditions are true in the ELSE. The End ends this CASE clause and AS NEWVAR is to have a column identified for the resulting value.

"Not working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

[Smitha9]

I tried this but its not pulling all the data especially .%

example:

582.7

582.0

582.3

is not pulled.

is there any other way in SAS? that I can use instead %?

[ballardw]

---

@Smitha9 wrote:

I tried this but its not pulling all the data especially .%

example:

582.7

582.0

582.3

is not pulled.

is there any other way in SAS? that I can use instead %?

---

Provide actual example data in the form of working data step code. SEE MY PREVIOUS comment about isn't working.

When I create a data set and use the code shown it "works".

data example;
   input code $;
datalines;
582.7
582.0
582.3
;

proc sql;
select 
case 
   when code like '43[0-8]%' then 'A'
   when code like '582.%' then 'B'
   when code like '58[56]%' then 'C'
   else ' ' 
end AS NEWVAR
from example
;
quit;

and the result is:

NEWVAR
B
B
B

If your variable is actually numeric "like" is not a good way to go. And show your code from your log.

[Kurt_Bremser]

---

@Smitha9 wrote:

I have the code in SQL. But  its not working in SAS procsql. Is there anyway that I could work it in SAS proc SQL?

select *

when code like '43[0-8]%' then 'A'

when code like '582.%' then 'B'

when code like '58[56]%' then 'C'

from table

;

quit;

---

This code won't work in any SQL dialect I know.

• no comma after the asterisk
• no CASE
• no END

Please take more care when posting your questions. After 100+ posts here, this should not be too hard.

[Ksharp]

It looks like a Perl Regular Express,try this :


select *
/*when code like '43[0-8]%' then 'A'*/
when prxmatch('/^43[0-8]/',code) then 'A'

/*when code like '582.%' then 'B'*/
when prxmatch('/^582\./',code) then 'B'

/*when code like '58[56]%' then 'C'*/
when prxmatch('/^58[56]/',code) then 'A'

from table

;