|
-
Today's tricky code request.......
Hi Experts!!
Had a request from another division that uses SAS. I've no idea about SAS, but here's the request......
---------------------------------------------------------------------
"I am trying to replicate our segmentation report which we run in SAS (see code below) but am having trouble creating the 'Segment' variable.
I have managed to create the tables Seg04, SumSeg12Cat60, SumSeg12Cat61 and SumSeg12Cat62, and have also managed to merge then together, but I can't see a way to create 'Segment'. I have looked for help everywhere I can think of but am struggling without the IF statement!"
Code:
data Seg04 (drop=Trans61 Spend61 Trans62 Spend62) ;
merge Seg04 (in=a) SumSeg12Cat60 (in=b) SumSeg12Cat61 (in=c) SumSeg12Cat62 (in=d) ;
by AcctNum ;
if a ;
if '01jul2004'd le DateOpen le '31dec2004'd then Segment = '2 Infant ' ; else
if '01jan2004'd le DateOpen le '30jun2004'd then Segment = '3 Adolescent ' ; else
if c and d then Segment = '4 Regular ' ; else
if not c and d then Segment = '5 Hot Casual ' ; else
if c and not d then Segment = '6 Cold Casual' ; else
if b then Segment = '7 Inactive 2 ' ; else
if NuLTDPur > 0 then Segment = '8 Inactive 3+' ; else
Segment = '9 Nevers ' ;
run ;
---------------------------------------------------------------------
There are 4 oracle tables with the driving table being Seg04. I suspect that a new column needs creating called SEGMENT on Seg04.
Then I need code that will replicate the SAS code above. I'm a bit out of my depth here, so any suggestions would be welcome.
-
Ask them for a translation in english, using sentences. Or have them read the sticky Jeff posted above.
-
For completeness, here's the PL/SQL code I came up with to replicate the SAS code functionality.........
Code:
drop table seg04_new;
CREATE TABLE SEG04_NEW
TABLESPACE SQL_USERS
NOLOGGING
PARALLEL
AS SELECT * FROM SEG04
WHERE 0=1;
ALTER TABLE SEG04_NEW ADD(SEGMENT VARCHAR2(20));
DECLARE
cursor c1 is select A.account_number AAN, A.date_opened ADO,
A.internal_status AIS,
A.date_first_purchase ADFP,
A.number_ltd_purchases ANLP,
B.account_number BBB,
C.account_number CCC,
D.account_number DDD
from seg04 A, sumseg12cat60 B, sumseg12cat61 C, sumseg12cat62 D
where A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER(+)
and A.ACCOUNT_NUMBER = C.ACCOUNT_NUMBER(+)
and A.ACCOUNT_NUMBER = D.ACCOUNT_NUMBER(+);
SEGMENT varchar2(20);
BEGIN
for rec1 in c1
loop
if
rec1.ADO >= TO_DATE('01-JUL-2004','DD-MON-YYYY') and rec1.ADO <= TO_DATE('31-DEC-2004','DD-MON-YYYY')
then
SEGMENT := '2 Infant';
elsif
rec1.ADO >= TO_DATE('01-JAN-2004','DD-MON-YYYY') and rec1.ADO <= TO_DATE('30-JUN-2004','DD-MON-YYYY')
then
SEGMENT := '3 Adolescent';
elsif
rec1.CCC = rec1.DDD
then
SEGMENT := '4 Regular';
elsif
rec1.CCC is null and rec1.DDD is not null
then
SEGMENT := '5 Hot Casual';
elsif
rec1.DDD is null and rec1.CCC is not null
then
SEGMENT := '6 Cold Casual';
elsif
rec1.BBB is not null
then
SEGMENT := '7 Inactive 2';
elsif
rec1.ANLP > 0
then
SEGMENT := '8 Inactive 3+';
else
SEGMENT := '9 Nevers';
end if;
execute immediate ('insert into seg04_new values(:U,
:V,
:W,
:X,
:Y,
:Z )')
using rec1.AAN,
rec1.ADO,
rec1.AIS,
rec1.ADFP,
rec1.ANLP,
SEGMENT;
end loop;
commit;
END;
/
-
Why would you do this in a PL/SQL loop, why not in a simple SQL INSERT-AS-SELECT statement?
Code:
insert into seg04_new
select A.account_number AAN,
A.date_opened ADO,
A.internal_status AIS,
A.date_first_purchase ADFP,
A.number_ltd_purchases ANLP,
case
when A.date_opened between TO_DATE('01-JUL-2004','DD-MON-YYYY') and TO_DATE('31-DEC-2004','DD-MON-YYYY')
then '2 Infant'
when A.date_opened between TO_DATE('01-JUL-2004','DD-MON-YYYY') and TO_DATE('31-DEC-2004','DD-MON-YYYY')
then '3 Adolescent'
when C.account_number = D.account_number
then '4 Regular'
when C.account_number is null and D.account_number is not null
then '5 Hot Casual'
when C.account_number is not null and D.account_number is null
then '6 Cold Casual'
when B.account_number is not null
then '7 Inactive 2'
when A.number_ltd_purchases > 0
then '8 Inactive 3+'
else '9 Nevers'
end SEGMENT
from seg04 A, sumseg12cat60 B, sumseg12cat61 C, sumseg12cat62 D
where A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER(+)
and A.ACCOUNT_NUMBER = C.ACCOUNT_NUMBER(+)
and A.ACCOUNT_NUMBER = D.ACCOUNT_NUMBER(+);
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Why not just?
Code:
INSERT /*+ APPEND */ INTO seg04_new
SELECT a.account_number, a.date_opened, a.internal_status,
a.date_first_purchase, a.number_ltd_purchases,
CASE
WHEN a.date_opened BETWEEN
TO_DATE ('01-JUL-2004', 'DD-MON-YYYY')
AND TO_DATE ('31-DEC-2004', 'DD-MON-YYYY') THEN
'2 Infant'
WHEN a.date_opened BETWEEN
TO_DATE ('01-JAN-2004', 'DD-MON-YYYY')
AND TO_DATE ('30-JUN-2004', 'DD-MON-YYYY') THEN
'3 Adolescent'
WHEN c.account_number = d.account_number THEN
'4 Regular'
WHEN c.account_number IS NULL
AND d.account_number IS NOT NULL THEN
'5 Hot Casual'
WHEN d.account_number IS NULL
AND c.account_number IS NOT NULL THEN
'6 Cold Casual'
WHEN b.account_number IS NOT NULL THEN
'7 Inactive 2'
WHEN a.number_ltd_purchase > 0 THEN
'8 Inactive 3+
ELSE
'9 Nevers'
END
FROM seg04 a, sumseg12cat60 b, sumseg12cat61 c, sumseg12cat62 d
WHERE a.account_number = b.account_number (+)
AND a.account_number = c.account_number (+)
AND a.account_number = d.account_number (+);
-
Of course, the good old CASE statement. What was I thinking. With the added bonus of the APPEND hint for minimal LOGGING activity.
Table creation reduced from 4 minutes to 20 seconds !!
Thanks Guys.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote