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;
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?
Depending on dataset sizes, you might be able to use memory-based techniques (format, hash object).
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: