BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CatM
Obsidian | Level 7

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]

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

6 REPLIES 6
Reeza
Super User
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...
CatM
Obsidian | Level 7

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 )

;

Reeza
Super User

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 )

;


 

CatM
Obsidian | Level 7

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

Reeza
Super User

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. 

CatM
Obsidian | Level 7

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!

 

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
  • 6 replies
  • 6160 views
  • 2 likes
  • 2 in conversation