how to transformation SQL into SAS

[harrylui]

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??

[ballardw]

Hard to tell without starting data and what the actual result should be.

A couple of comments though.

NULL is really a SAS concept better instead of: AND ACRS.Cancellation_Effective_Date IS NULL

would be :  and missing( ACRS.Cancellation_Effective_Date)

This bit

ACRS.End_Date LIKE '2017-06%'

brings up a question of is the variable character or numeric in the SAS data set? If character then the above would work. If the SAS variable is a SAS date value then this looks like you are looking for year=2017 and month=6

The code that appears similar in your attempted SAS code seems to be:

ACRS.End_Date <='30SEP2018'D

which assumes the variable is a SAS date value, fine so far, but is requesting ALL values prior (and a different year and month). So the question becomes are you looking for values in a SPECFIC month such as the previous SQL or prior to a given date?

I don't know what the PARTITION BY does so can't comment as to your approach working or not.

When you run your code do you get Errors or Warnings or in the log? If so copy the code generating the error / warning along with the error/warning text and paste it into a code box opened using the forum's {I} to preserve formatting of the errors.

If you aren't getting errors but the result is not what you expect then you need to provide a small example of the starting data and what the result should be. If your data includes many variables not actually used by the code you only need to include one or two of the variables.

Best is to provide data as data step: Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[kiranv_]

i had oppurtunity to change bunch of SAS code into DBMS specific sql. I have learned one thing, emulating sort is impossible from SAS to DBMS or vice versa unless you are using whole row as part of your sort. Let me explain through an example.

data have;
input id age col1 col2 col3;
datalines;
1 30 1 1 3
1 29 2 2 3
1 29 1 1 1
1 30 1 1 7
2 40 1 1 1
2 40 1 2 3
3 25 1 5 7
;

proc sort data =have
out=have1;
by id age;
run;
data want;
set have1;
by id;
if first.id then rownum=1;
ELSE rownum+1;
run;

/*output is*/


Obs	id	age	col1	col2	col3	rownum
1	1	29	2	2	3	1
2	1	29	1	1	1	2
3	1	30	1	1	3	3
4	1	30	1	1	7	4
5	2	40	1	1	1	1
6	2	40	1	2	3	2
7	3	25	1	5	7	1

/*I ran the same equivalent code in DBMS(used Postgres)*/


create table have
("id" int,
age int,
col1 int,
col2 int,
col3 int);

insert into have values (1, 30, 1, 1, 3);
insert into have values(1, 29, 2, 2, 3);
insert into have values (1, 29, 1, 1, 1);
insert into have values (1, 30, 1, 1, 7);
insert into have values (2, 40, 1, 1, 1);
insert into have values (2, 40, 1, 2, 3);
insert into have values (3, 25, 1, 5, 7);

select *, row_number() over(partition by id order by age) as rownum from have;

/*output*/
id     age     col1     col2    col3    rownum
1	29	1	1	1	 1
1	29	2	2	3	 2
1	30	1	1	7	 3
1	30	1	1	3	 4
2	40	1	1	1	 1
2	40	1	2	3	 2
3	25	1	5	7	 1

 you will see the answer make sense in both code, but both results are not same especially for ID 1. I did not go through your code for other aspects, but just sharing one of my experiences