topic Re: Convert code SQL to SAS in SAS Programming

Convert code SQL to SAS

user24 — Wed, 28 Oct 2015 13:03:28 GMT

I have a sql code as below:

select tbraccd_Detail_code "detail code",tbraccd_term_code "Term",tbraccd_effective_date "Effective_Date",

sum (tbraccd_balance) "Balance"

from spriden, tbraccd,TBBDETC

where spriden_pidm = tbraccd_pidm

and tbbdetc_detail_code = tbraccd_detail_code

and tbraccd_term_code = '201610'

and TBBDETC_DCAT_CODE = 'IPL'

and spriden_change_ind is null

having sum(tbraccd_balance) <>0

and spriden_id = 'V033796'

group by spriden_id,tbraccd_detail_code,tbraccd_term_code,tbraccd_effective_date

order by tbraccd_effective_date desc

I need to convert this to SAS code. i converted as below but solution is not same. Where is my mistake?

select
tbraccd_Detail_code as detail_code,
tbraccd_term_code as Term,
tbraccd_effective_date as Effective_Date,

sum (tbraccd_balance) as Balance

from stg.spriden, stg.tbraccd,stg.TBBDETC

where spriden_pidm = tbraccd_pidm
and tbbdetc_detail_code = tbraccd_detail_code

and TBBDETC_DCAT_CODE = 'IPL'
and tbraccd_term_code= "201610"

and spriden_id = 'V033796'
and spriden_change_ind is null
group by spriden_id,tbraccd_detail_code,tbraccd_term_code,tbraccd_effective_date

having sum(tbraccd_balance) <>0
order by tbraccd_effective_date desc
;quit;

Re: Convert code SQL to SAS

arodriguez — Wed, 28 Oct 2015 13:09:04 GMT

Wich is the SAS ERROR that appears to you?

Re: Convert code SQL to SAS

user24 — Wed, 28 Oct 2015 13:10:58 GMT

No error, but solution is not same.

Re: Convert code SQL to SAS

Steelers_In_DC — Wed, 28 Oct 2015 13:21:12 GMT

Your having statement and group by statement are in different orders. I would try switching that and see if it solves your problem.

Re: Convert code SQL to SAS

MaikH_Schutze — Wed, 28 Oct 2015 13:23:33 GMT

I rarely use SQL, therefore this might be an elementary question but I assume you are using your code with a PROC SQL statement, is that correct. SAS has implemented SQL using PROC SQL and as far as I understand, the syntax should be the same.

Re: Convert code SQL to SAS

RW9 — Wed, 28 Oct 2015 13:24:03 GMT

Is there a reason why you moved one of the restrictions:

and spriden_id = 'V033796'

From the having clause in the first, to the where in second, may impact.

Re: Convert code SQL to SAS

MaikH_Schutze — Wed, 28 Oct 2015 13:26:53 GMT

Good observation. Again, I don't use PROC SQL often, but the GROUP BY clause has to come before the HAVING clause.

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473678.htm

SELECT <DISTINCT> object-item <, ...object-item>

FROM from-list

<ORDER BY order-by-item
< , ... order-by-item>>;

Re: Convert code SQL to SAS

Steelers_In_DC — Wed, 28 Oct 2015 13:31:21 GMT

If the previous application you were using allowed the having and group by clause to be reversed that still looks like the issue. I would try making this two seperate queries.

Re: Convert code SQL to SAS

Tom — Wed, 28 Oct 2015 13:39:46 GMT

Check you missing data patterns. SAS will consider missing values as equal and other databases normally use tri-level logic where null values cause comparison to be neither true nor false , even when comparing null to null.

Also I would not use <> as an operator. I think in PROC SQL it will be treated as meaning not equal, but in normal SAS it is the maximum operator.

Re: Convert code SQL to SAS

LinusH — Wed, 28 Oct 2015 16:49:31 GMT

My usual question: Why do you want to make this a Data step?