BookmarkSubscribeRSS Feed
Ram4sas
Fluorite | Level 6

Hi,

 

I am running out with below Syntax error message for SAS Proc Sql code.

 

the below code is working fine in Sql developer and shared with SAS user to run and collect result,

but the code through error for sql functions - over, count and (+) in below code.

 

====================================

select name,cde_fund_code,id_provider,D.num_check, sak,A.sAcct_rec, C.chk_sak,amt_setup,amt,B.dte_payment_issue,amt_setup -sum(amt)
over (PARTITION BY A.sak_Acct_rec order by dte_payment_issue ),
count(amt) over (PARTITION BY A.sak_Acct_rec order by dte_payment_issue )
from A, B, C, D
where A.s_fund_code in ( '****','****')
and B.acct_rec (+) = A.acct_rec
and C.sak (+) = B.sak
and D.chk_sak (+) = C.chk_sak;

 

is there any different function to use SAS Proc sql? because its working fine in Sql Developer.

 

How to fix this and run the code withut errors?

4 REPLIES 4
Reeza
Super User
SAS doesn't support the same functions as SQL Developer so code that works in Developers shouldn't be expected to work by default in SAS.

Unless you use SQL Pass Through, which will pass the code directly to the SQL Server.
http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#p1f29m86u65hken1de...

Ram4sas
Fluorite | Level 6

Hi Reeza,

 

Thank you for your reply, But the pass through facility needs users to be supplied with DB log in User ID and PW. that is security issues.

 

I would like to know how to submit the SQL Pass Through facility code without sharing DB Log on Parameters to user.

 

is there any way to store the log on UserID/PW in server configuration and share that config reference with users to run Pass through code with that refenrece as part of code?

 

 

Please me know if my question is not clear. 

 

Thank,

Ram

Reeza
Super User
Your question is clear, but not one I can answer unfortunately. I think that's a server set up? You may be able to set up users with single sign on and use the DSN setup on their computers, but they would still have access even if they knew the password so I'm not sure what difference that makes. Their user ID on the server should be restricted to read only.
Patrick
Opal | Level 21

The users don't need to know the credentials for the DB Server.

http://support.sas.com/documentation/cdl/en/bisecag/67045/HTML/default/viewer.htm#n0wbhwrrnxrrudn1gh...

 

If you don't set-up things as above then you'd also have the same issue with implicit SQL and your libname statement as it's not hard to query a libname definition.

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 4 replies
  • 1658 views
  • 1 like
  • 3 in conversation