topic Re: SQL to SAS in SAS Programming

SQL to SAS

Ranjeeta — Tue, 04 Dec 2018 16:49:03 GMT

Hello,

Can someone advise how I would write the SQL code below in SAS? I am not aware of SQL and how to read the programs accurately and then perform the associated actions in SAS

Regards

PROC SQL;
CREATE TABLE CR_FY1217 AS
SELECT DISTINCT
CASE
WHEN '01APR2012'D<=DATEPART(REMOVALDATE)<='31MAR2013'D THEN 2012
WHEN '01APR2013'D<=DATEPART(REMOVALDATE)<='31MAR2014'D THEN 2013
WHEN '01APR2014'D<=DATEPART(REMOVALDATE)<='31MAR2015'D THEN 2014
WHEN '01APR2015'D<=DATEPART(REMOVALDATE)<='31MAR2016'D THEN 2015
WHEN '01APR2016'D<=DATEPART(REMOVALDATE)<='31MAR2017'D THEN 2016
WHEN '01APR2017'D<=DATEPART(REMOVALDATE)<='31MAR2018'D THEN 2017
ELSE .
END AS FYEAR,
DATEPART(REMOVALDATE) AS REMOVAL_DT FORMAT=DATE9.,
A.WAITTIMEPATIENTID,
A.WAITTIMEPROCEDURECD,
A.WAITLISTENTRYID,
A.PRIORITYLEVELCD,
B.GENDERCD,
B.ADDRESSUNAVAILABLEIND,
SUM(C.COUNTRYCD='CAN')>0 AS FLAG_CANADA, /* MULTIPLE PROVINCE RECORDS */
SUM(C.PROVINCECD='CA-ON')>0 AS FLAG_ONTARIO, /* MULTIPLE PROVINCE RECORDS */
F.CAUSEOFDEATHCD,
F.PLACEOFDEATHCD,
F.DEATHWITHINSAMEREGIONCARECD,
F.AUTOPSYCD
FROM CHO1.CARDIACWAITLISTENTRY AS A
LEFT JOIN CHO1.WAITTIMEPATIENT AS B
ON A.WAITTIMEPATIENTID=B.WAITTIMEPATIENTID
LEFT JOIN CHO1.WAITTIMEPATIENTADDRESS AS C
ON A.WAITTIMEPATIENTID=C.WAITTIMEPATIENTID
LEFT JOIN CHO1.WAITLISTENTRYCANCELLATION AS D
ON A.WAITLISTENTRYID=D.WAITLISTENTRYID
LEFT JOIN CHO1.CARDIACOFFLISTINGDETAILS AS E
ON A.WAITLISTENTRYID=E.WAITLISTENTRYID
LEFT JOIN CHO1.CARDIACSURVEILLANCESUMMARY AS F
ON E.CARDIACOFFLISTINGDETAILSID=F.CARDIACOFFLISTINGDETAILSID
GROUP BY A.WAITLISTENTRYID
HAVING '01APR2012'D<=REMOVAL_DT<='31MAR2018'D
ORDER BY REMOVAL_DT;
QUIT;

Re: SQL to SAS

Reeza — Tue, 04 Dec 2018 17:05:31 GMT

That is SAS code. Rewriting this in a data step would be a fairly difficult thing to do. You're merging 6/7 tables with different keys, and each set of different keys means a new data step. PROC SQL is most definitely the best solution in this case.

It's worth doing some reading on SQL, if you're familiar with data analysis, a day will let you learn about 80% of what you need for SQL.

http://www2.sas.com/proceedings/sugi27/p191-27.pdf

Webinar/video:
https://www.sas.com/en_us/webinars/sql-introduction/register.html

Re: SQL to SAS

Ranjeeta — Tue, 04 Dec 2018 17:30:19 GMT

Thankyou !! Apprecite it

Re: SQL to SAS

Ranjeeta — Tue, 04 Dec 2018 18:49:19 GMT

what does it mean when you say group by 1 or 2 what do the numbers stand for in a sql code?

Re: SQL to SAS

Reeza — Tue, 04 Dec 2018 18:52:03 GMT

1 and 2 are positional indicators that refer to the first and second variable in the table.

@Ranjeeta wrote:
what does it mean when you say group by 1 or 2 what do the numbers stand for in a sql code?

Re: SQL to SAS

Kurt_Bremser — Wed, 05 Dec 2018 00:12:03 GMT

Depending on dataset sizes, you might be able to use memory-based techniques (format, hash object).