topic Re: CONVERT SQL SERVER CODE TO SAS in SAS Programming

CONVERT SQL SERVER CODE TO SAS

CatM — Tue, 12 Jun 2018 21:22:57 GMT

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]

Re: CONVERT SQL SERVER CODE TO SAS

Reeza — Tue, 12 Jun 2018 21:35:00 GMT

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&docsetVersion=9.4&locale=en

Re: CONVERT SQL SERVER CODE TO SAS

CatM — Tue, 12 Jun 2018 21:40:08 GMT

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 )

;

Re: CONVERT SQL SERVER CODE TO SAS

Reeza — Tue, 12 Jun 2018 21:54:40 GMT

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 )

;

Re: CONVERT SQL SERVER CODE TO SAS

CatM — Tue, 12 Jun 2018 22:02:58 GMT

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

Re: CONVERT SQL SERVER CODE TO SAS

Reeza — Tue, 12 Jun 2018 22:09:10 GMT

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.

Re: CONVERT SQL SERVER CODE TO SAS

CatM — Tue, 12 Jun 2018 22:11:06 GMT

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!