CONVERT SQL SERVER CODE TO SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

CONVERT SQL SERVER CODE TO SAS

Hello!

 

I am trying to convert some SQL Server code into a SAS proc SQL format but there are a couple of things I have not figured out how to code. Below is the code that I have in SQL server. I know there is an insert function in proc sql for sas but i am not sure how that applies. I have to connect through an odbc connection to gain access to the WWH server where the data is stored so i am not sure how that also changes the code. i have tried to combine the code below into a single proc sql create table statement but it has not been successful. I have also tried to individually create the tables but since some are dependent on the ID from the larger set to select the correct data, i cannot really pull in the entire tables individually and merge later. The declare code i am assuming is like a format step in sql server to assign the variable a length and format. And i am not sure how the quarter variable is being created but i can recreate that from our dates in a separate step. thanks! 

 

 

DECLARE @AllINF Table  (FileDate char(7),IndividualID char(11),ClinicID smallint,[Quarter] varchar(10),RMonth smallint)

DECLARE @RiskINF Table  (FileDate char(7),IndividualID char(11),ClinicID smallint,[Quarter] varchar(10),RMonth smallint,

                     MinOdds float, MaxOdds float, OddsRecs int, IsCert int)

 

** create the table of those to keep;

INSERT INTO @AllINF (FDate,IndivID,ClinicID,[Quarter],RMonth)

SELECT I.FDate,I.IndivID,I.ClinicID,Q.Quarter,Q.RMonth

FROM [WWH].[dbo].IND3_V  I

       JOIN dbo.Dates Q

              ON I.FDate = Q.DateText

WHERE I.Months = 5

AND      I.Categ = 'I'  AND I.ClinicID in (7,4,12,18,10)

AND   I.FDate BETWEEN '2016-01' and '2018-03'

 

 

**find their odds of staying over the last six months as an infant.;

** look at age 14 months to determine if they stayed.;

INSERT INTO @RiskINF (IndivID,ClinicID, [Quarter], RMonth, MaxOdds, MinOdds, OddsRecs, IsCert)

SELECT A.IndivID,A.ClinicID,A.[Quarter],A.RMonth,MAX(Odds.STAY) MaxOdds,MIN(Odds.STAY) MinOdds,Count(*) OddsRecs,

              MAX(IsCert) IsCert

FROM   @AllINF A

       JOIN WWH.dbo.ExtOdds Odds

              ON A.IndivID = Odds.IndivID

       JOIN dbo.Dates D

              ON Odds.FDate = D.DateText

              AND D.RMonth BETWEEN A.RMonth-5 AND A.RMonth

       JOIN dbo.Dates Test

              ON A.RMonth = Test.RMonth - 2              

       JOIN WWH.dbo.IND3_V I

              ON I.FileDate = Test.DateText

              AND I.IndivID = A.IndivID

GROUP BY A.IndivID,A.ClinicID,A.[Quarter],A.RMONTH      

Having Count(*) = 6 AND MIN(Odds.Stay) < 1.25

 

SELECT [Quarter],ClinicID, Count(*) INF, Sum(Cert) Cert

FROM   @RiskINF

GROUP BY [Quarter],ClinicID

ORDER BY ClinicID,[Quarter]


Accepted Solutions
Solution
‎06-12-2018 06:11 PM
Super User
Posts: 23,689

Re: CONVERT SQL SERVER CODE TO SAS

Use native PROC SQL?

 

libname sData ODBC dsn=your_dsn schema=dbo;

proc sql;
create table want as 
select * 
from sData.Table1 as t1
left join whh.Table2 as t2
on t1.key=t2.key;
quit;

Depending on how big your data sets are, this may work well or may not. 

View solution in original post


All Replies
Super User
Posts: 23,689

Re: CONVERT SQL SERVER CODE TO SAS

Check the examples section of the documentation to see how things work. The first example is about creating a table and INSERT to create data.
http://documentation.sas.com/?docsetId=sqlproc&docsetTarget=n1ncn0pznd8wrln1tnp3xdxjz9xz.htm&docsetV...
Occasional Contributor
Posts: 7

Re: CONVERT SQL SERVER CODE TO SAS

thanks i understood that part but i have to connect to odbc so its not as simple as just calling up the tables from a library. i wanted to know how to do this with and odbc connection to call up the tables from the WWH server. I keep getting errors when i try to use an Insert into statement so i need to know what i am doing wrong. 

 

this is how i normally connect 

 

libname data "G:\catdata";


proc sql;
CONNECT to odbc(dsn=WWH);
CREATE TABLE Data.TEST1 as SELECT * from connection to odbc
(SELECT * from WWH.dbo.IND3_V )

;

Super User
Posts: 23,689

Re: CONVERT SQL SERVER CODE TO SAS

Actually, that type of SQL is pass through SQL and that requires that you use the native language on the database. All SAS does when you use that type of connection is pass the code directly to the server. However, you do need to ensure that the types are correct and that you're only referencing data on the server. 

 

If you need to mix data from your local drive, a different server, and the ODBC connection you need a different approach. 

 


@CatM wrote:

thanks i understood that part but i have to connect to odbc so its not as simple as just calling up the tables from a library. i wanted to know how to do this with and odbc connection to call up the tables from the WWH server. I keep getting errors when i try to use an Insert into statement so i need to know what i am doing wrong. 

 

this is how i normally connect 

 

libname data "G:\catdata";


proc sql;
CONNECT to odbc(dsn=WWH);
CREATE TABLE Data.TEST1 as SELECT * from connection to odbc
(SELECT * from WWH.dbo.IND3_V )

;


 

Occasional Contributor
Posts: 7

Re: CONVERT SQL SERVER CODE TO SAS

OK thank you, 

 

what would be the best option to merge the local dataset with the data from an ODBC connection? i have been attempting to use the create table command rather than the insert into in sql but i still cannot get the data to merge because as you said it is two different types of locations the data is saved under. 

 

Thank you for all your help

Solution
‎06-12-2018 06:11 PM
Super User
Posts: 23,689

Re: CONVERT SQL SERVER CODE TO SAS

Use native PROC SQL?

 

libname sData ODBC dsn=your_dsn schema=dbo;

proc sql;
create table want as 
select * 
from sData.Table1 as t1
left join whh.Table2 as t2
on t1.key=t2.key;
quit;

Depending on how big your data sets are, this may work well or may not. 

Occasional Contributor
Posts: 7

Re: CONVERT SQL SERVER CODE TO SAS

perfect! that's a similar step i took, i connected using the library statement with my poor code just using a 

 

libname mydata odbc dsn=WWH;

 

but your refined code is amazing, thank you!

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 111 views
  • 2 likes
  • 2 in conversation