sas proc sql equivalent to oracle CONNECT BY

[sandradinis]

Hello,

I am a SAS user, and I have a sql code which I don't know how to translate to SAS CODE. Can you help me?

(SELECT DISTINCT E.COMMENT FROM table E,(SELECT DECODE(level,1, 'A',
2, 'B',
3, 'C',
4, 'D',
5, 'E',
6, 'F',
7, 'G',
8, 'H',
9, 'I',
10, 'J',) n
FROM dual
CONNECT BY level <=10) l
WHERE E.COMMENT LIKE l.n)

Thanks in advanced 🙂

[kiranv_]

please give sample  input and output, someone will help you. 

[sandradinis]

the idea is to query this into a not in clause... like:

SELECT *
FROM table CB
LEFT JOIN CODE_TABLE CCBR on (CB.CODE_ID = CCBR.ID)
WHERE CB.CODE_ID NOT IN (108, 112, 113, 110) AND
CB.COMMENT not in((SELECT DISTINCT E.COMMENT FROM table E,(SELECT DECODE(level,1, 'A',
2, 'B',
3, 'C',
4, 'D',
5, 'E',
6, 'F',
7, 'G',
8, 'H',
9, 'I',
10, 'J',) n
FROM dual
CONNECT BY level <=10) l
WHERE E.COMMENT LIKE l.n)

[RW9]

What do you mean by " translate to SAS CODE"?  Do you want a datastep?  Or do you mean ANSI SQL?  Where is the data located, is it in a database, or in a SAS dataset?  Do you have any documentation on what that code is supposed to do?

I mean I can give some pointers, but hard to re-factor code without knowing really anything about it.

decode(<var>,<code>,<decode>[,<code>,<decode>]) - this is basically an if then block.  If var = code then the decode value will be returned.  You can do this in ansi sql with case statements, e.g.

case level when 1 then "A"
                 when 2 then "B"
                 ...
                 else . end as n

(note that I don't shout my code at you).

Dual is not in ansi sql would need to know how it is being used in this instance.

Connect by I have never used, nor seen used.  I presume its like a join.

The rest of the code is pretty self explanatory.

[sandradinis]

Hi,

Maybe i have not explained myself well, I need to translate the following query to sas code. I have in sas the datasets that correspond to the oracle tables. But since I do not know very well what the connect by does in oracle, I do not know how to translate it.

Oracle query:

SELECT DISTINCT *
FROM k.CRAN_TABLE CB
LEFT JOIN k.CODE_CRAN_REASON CCBR on (CB.REASON_ID = CCBR.ID)
WHERE (CB.REASON_ID <> 109 OR (CB.REASON_ID = 109 and
CB.COMMENT not like (SELECT DISTINCT E.COMMENT FROM k.CRAN_BLACK E,(SELECT DECODE(level,1, 'zz',
2, 'aa',
3, 'bb',
4, 'cc',
5, 'dd',
6, 'ee',
7, 'ff',
8, 'gg',
9, 'ii',
10, 'jj',
) n
FROM dual
CONNECT BY level <=10
) l
WHERE E.COMMENT LIKE l.n))));

somthing like doing in the where clause:

reason_id <> 109 or reason_id = 109 and comment_open not like '%DESC%'

But the '%DESC%' can have the following values:

'zz', 
'aa',
'bb',
'cc',
'dd',
'ee',
'ff',
'gg',
'ii',
'jj',

maybe there is an easier way to do it, but I do not know how.

[RW9]

You keep referring to "sas code", but SAS code is datastep.  There is a proc sql component of Base SAS which can run ANSI SQL statements.  As for what connect by does, as I said, I have never used or seen it used.  Its not a SAS function, therefore asking here really wont help much.  Better to read up on the oracle sites:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:489772591421

And then start looking at ways of doing something similar in SAS.