BookmarkSubscribeRSS Feed
user24
Obsidian | Level 7

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;

9 REPLIES 9
arodriguez
Lapis Lazuli | Level 10

Wich is the SAS ERROR that appears to you?

user24
Obsidian | Level 7
No error, but solution is not same.
Steelers_In_DC
Barite | Level 11

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

MaikH_Schutze
Quartz | Level 8

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>>;
Steelers_In_DC
Barite | Level 11

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.

MaikH_Schutze
Quartz | Level 8

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

 

Tom
Super User Tom
Super User

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.

LinusH
Tourmaline | Level 20
My usual question: Why do you want to make this a Data step?
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1486 views
  • 1 like
  • 7 in conversation