Convert code SQL to SAS

Reply
Contributor
Posts: 33

Convert code SQL to SAS

[ Edited ]

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;

Frequent Contributor
Posts: 144

Re: Convert code SQL to SAS

Wich is the SAS ERROR that appears to you?

Contributor
Posts: 33

Re: Convert code SQL to SAS

Posted in reply to arodriguez
No error, but solution is not same.
Valued Guide
Posts: 860

Re: Convert code SQL to SAS

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

Contributor
Posts: 45

Re: Convert code SQL to SAS

Posted in reply to Steelers_In_DC

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>

 
  <INTO macro-variable-specification
< , ... macro-variable-specification>>
  FROM from-list
  <WHERE sql-expression>
  <GROUP BY group-by-item
< , ... group-by-item>>
  <HAVING sql-expression>
  <ORDER BY order-by-item
< , ... order-by-item>>;
Valued Guide
Posts: 860

Re: Convert code SQL to SAS

Posted in reply to MaikH_Schutze

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.

Contributor
Posts: 45

Re: Convert code SQL to SAS

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.

Super User
Super User
Posts: 7,971

Re: Convert code SQL to SAS

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.  

 

Super User
Super User
Posts: 7,060

Re: Convert code SQL to SAS

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.

Super User
Posts: 5,431

Re: Convert code SQL to SAS

My usual question: Why do you want to make this a Data step?
Data never sleeps
Ask a Question
Discussion stats
  • 9 replies
  • 610 views
  • 1 like
  • 7 in conversation