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;
Wich is the SAS ERROR that appears to you?
Your having statement and group by statement are in different orders. I would try switching that and see if it solves your problem.
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>
|
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.
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.