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]
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.
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 )
;
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 );
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
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.
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.