Proc sql Group By

Ranjeeta — Thu, 06 Jun 2019 17:00:48 GMT

PROC SQL;
CREATE TABLE PBF_DESTINATION AS
SELECT DISTINCT
A.*,
MIN(DATEPART(D.ADM_DT)) AS TRANS_ENTRY FORMAT=DATE9.,
MAX(DATEPART(D.DISCH_DT)) AS TRANS_DISCH FORMAT=DATE9.,
FAC_NAME AS TRANS_FAC,
MIN(C.DT_ENTRY) AS CCC_ENTRY FORMAT=DATE9.,
MAX(C.DT_DISCH) AS CCC_DISCH FORMAT=DATE9.,
MIN(N.DT_ENTRY) AS NRS_ENTRY FORMAT=DATE9.,
MAX(N.DT_DISCH) AS NRS_DISCH FORMAT=DATE9.,
MIN(L.DT_ENTRY) AS LTC_ENTRY FORMAT=DATE9.,
MAX(L.DT_DISCH) AS LTC_DISCH FORMAT=DATE9.,
MIN(CALCULATED TRANS_ENTRY, CALCULATED CCC_ENTRY, CALCULATED NRS_ENTRY, CALCULATED LTC_ENTRY)
AS MIN_ENTRY FORMAT=DATE9.,
MISSING(D.HCNE)=0 AND (MIN(DATEPART(D.ADM_DT)) <= CALCULATED MIN_ENTRY)
AS ACUTE_TRANS 'Transferred to Another Acute Facility (Based on data linkages)',
MISSING(C.HCNE)=0 AND (MIN(C.DT_ENTRY) <= CALCULATED MIN_ENTRY)
AS CCC_TRANS 'Discharged to CCC (Based on data linkages)',
MISSING(N.HCNE)=0 AND (MIN(N.DT_ENTRY) <= CALCULATED MIN_ENTRY)
AS NRS_TRANS 'Discharged to IP Rehab (Based on data linkages)',
MISSING(L.HCNE)=0 AND (MIN(L.DT_ENTRY) <= CALCULATED MIN_ENTRY)
AS LTC_TRANS 'Discharged to LTC (Based on data linkages)'
FROM PBF_STROKE AS A
LEFT JOIN DAD_FY16_18Q2 AS D
ON A.HCNE=D.HCNE AND A.HCNE~='9999999999'
AND 0<=(DATEPART(D.ADM_DT)-DATEPART(A.DISCH_DT))<=1
AND A.CIHI_KEY~=D.CIHI_KEY
LEFT JOIN CCC AS C
ON A.HCNE=C.HCNE AND A.HCNE~='9999999999'
AND 0<=(C.DT_ENTRY-DATEPART(A.DISCH_DT))<=1
LEFT JOIN NRS AS N
ON A.HCNE=N.HCNE AND A.HCNE~='9999999999'
AND 0<=(N.DT_ENTRY-DATEPART(A.DISCH_DT))<=1
LEFT JOIN LTC AS L
ON A.HCNE=L.HCNE AND A.HCNE~='9999999999'
AND 0<=(L.DT_ENTRY-DATEPART(A.DISCH_DT))<=1
WHERE A.FYEAR IN (2016, 2017)
GROUP BY A.CIHI_KEY
ORDER BY A.CIHI_KEY;
QUIT;

Hello Can someone advise where in the code the variable in the group by clause (CIHI_KEY) is summarized as I believe the variable included in the group by clause need to refer to a summary function

Thanks for yor help in trying to understand the code

Re: Proc sql Group By

Haris — Thu, 06 Jun 2019 19:52:44 GMT

All of your summary functions will be calculated separately for each level of the GROUP BY variable. Each row will receive a different value of Trans_Entry date that corresponds to the minimum Adm_Dt for that group and so on.

Re: Proc sql Group By

Ranjeeta — Thu, 06 Jun 2019 20:26:10 GMT

Thankyou

topic Re: Proc sql Group By in SAS Programming

Proc sql Group By

Re: Proc sql Group By

Re: Proc sql Group By