HELLO,
below is my SQL Code and i have to change it into SAS code
SELECT
ACRS.*
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY SUBSTRING(ACRS.Policy_No,1,14) ORDER BY ACRS.Issuance_Date DESC, ACRS.Issuance_Time DESC) as id,
ACRS.Policy_No as PolicyNo
FROM ACRS
WHERE SUBSTRING(ACRS.Policy_No,1,14) IN (SELECT SUBSTRING(ACRS.Policy_No,1,14) FROM ACRS WHERE ACRS.[Plan] Like '%Annual%'
AND ACRS.Product IN ('TravelWell Annual Trip', 'Overseas Education Protection')
GROUP BY SUBSTRING(ACRS.Policy_No,1,14) HAVING SUM(ACRS.Total_Premium) > 0)
) L JOIN ACRS
ON L.PolicyNo = ACRS.Policy_No
WHERE L.id =1
AND (ACRS.End_Date LIKE '2017-06%')
AND ACRS.Cancellation_Effective_Date IS NULL
ORDER BY ACRS.End_Date;
and below is my SAS Code but i think there is something wrong because I have messed up with the order to make it can run in sas form
data ACRS;
set ACRS_RAW3;
NewPolicyNo=SUBSTR(Policy_No,1,14);
run;
PROC SQL;
CREATE TABLE ACRS2 AS
SELECT * FROM ACRS
WHERE Plan Like '%Annual%'
AND Product IN ('TravelWell Annual Trip', 'Overseas Education Protection')
GROUP BY NewPolicyNo
HAVING SUM (Total_Premium) > 0;
QUIT;
PROC SORT DATA=ACRS2;
BY NewPolicyNo DESCENDING Issuance_Date DESCENDING Issuance_Time;
run;
data ACRS3;
set ACRS2;
BY NewPolicyNo;
if first.NewPolicyNo then ID=1;
ELSE ID+1;
run;
PROC SQL;
CREATE TABLE ACRS4 AS
SELECT * FROM ACRS3
LEFT JOIN ACRS
ON ACRS3. Policy_No = ACRS. Policy_No
WHERE ACRS3.id =1
AND ACRS.End_Date <='30SEP2018'D
AND ACRS.Cancellation_Effective_Date IS NULL
ORDER BY ACRS.End_Date
;
quit;
can anyone help me with this if i am wrong in somewhere??