umashankersaini Tracker

Want to write sql query to get related party account details

umashankersaini — Mon, 08 Apr 2024 12:12:42 GMT

Hi Team,

Greetings for the day!

Please help me to write a sql to get the related party details.

For example suppose i have opened my account with mobile and email.

My wife has used my mobile number while opening her account and i have used my email id for my daughter's account.

In this case my wife's account is related with my mobile and daughter's account is related with my email id and scenario could be daughter has used wife's email.

So if i am searching for my account, i should get wife's and daughter's account as well.

I have tried to prepare the sample data with result output:

Sample :

ID	Phone	Email
5534385355	385534616655	ABC@GMAIL.COM
5548558464	3588333353	ABC12.com
5564556334	6366636666	ABC2@gmail.com
554814331	6001066401	ABC10.com
5564556331	6366636666	ABC3@gmail.com
5548558456	68136551346	ABC7.com
5538083556	6066688561	ABC4D.COM
554816644	68141055686	ABC9.com
5563065535	6355555563486	ABC5@yahoo.com
55155355386	6460535366	ABC11.com
553438530	385534616655	ABC1@GMAIL.COM
554884666	6354641861	ABC13.com
5534385355	385534616655	ABC@GMAIL.COM
555336340	6836833860	ABC6.com
554363456	6066688561	ABC4D.COM
55464315555	6863083133	ABC8.com
556343006	6355555563486	ABC5@yahoo.com

Output :

ID	Phone	Email
5534385355	385534616655	ABC@GMAIL.COM
553438530	385534616655	ABC1@GMAIL.COM
5564556334	6366636666	ABC2@gmail.com
5564556331	6366636666	ABC3@gmail.com
5538083556	6066688561	ABC4D.COM
554363456	6066688561	ABC4D.COM
556343006	6355555563486	ABC5@yahoo.com
5563065535	6355555563486	ABC5@yahoo.com

Please help.

Regards,

Uma Shanker Saini

Tagging customer's transaction to latest campaign

umashankersaini — Sat, 12 Dec 2020 19:13:27 GMT

Hi Team,

I need to tag customer's transaction to the latest campaign only.

Sample files has been attached with desired result.

let me explain scenario little bit.

Suppose i am sending a campaign on 27NOV2020 (EVENT_CAPTURED_DT) with attribution window 27Nov2020+7days =04DEC2020(CPGN_END_DT). Now customer has shopped on 06DEC which is not part of mentioned campaign.

In this case customer would get tagged to another campaign which has end_date greater that transaction date.

In case i have sent multiple campaigns to same customer in a week (different dates) and customer has shopped then customer's transaction would get tagged to last campaign (latest).

Sample Files:

CPGN_ORIG - Contains campaign details

ATTRN_ATTRB - Customer's shopping details during attribution window

CPGNS_Final_Output - Required output - Customer's transaction tagged to latest campaign - sample example

Proc sql would be first choice as a code. Kindly read the attached file for sample data.

Thanks in advance.

Regards,

Uma Shanker Saini

How to calculate moving total amount for one year with group by facility

umashankersaini — Mon, 20 Jan 2020 17:18:04 GMT

Hi Team,

I need to calculate total amount spend by each customer for 365 days where 12 months should be calculated from first purchase made by customer during the campaign window.

i have calculated the first purchase date during the campaign window but unable to pass the dates into where clause.

Kindly have a look into sample data

CPGNID	CUSTOMER_ID	CPGN_STRT_DT	CPGN_END_DT	Tran_dt_During_CPGN
106248162U	A1000000050	27-Dec-19	3-Jan-20	.
106249382U	A1000000050	3-Jan-20	10-Jan-20	.
106252082U	A1000000050	19-Jan-20	26-Jan-20	.
106248382U	A1000001868	28-Dec-19	4-Jan-20	.
106251782U	A1000001868	18-Jan-20	25-Jan-20	.
106248242U	A10000303	27-Dec-19	3-Jan-20	28-Dec-19
106249462U	A10000303	3-Jan-20	10-Jan-20	.
106251502U	A10000303	17-Jan-20	24-Jan-20	.
106248462U	A1000100052	28-Dec-19	4-Jan-20	2-Jan-20
106249462U	A1000100052	3-Jan-20	10-Jan-20	4-Jan-20
106251502U	A1000100052	17-Jan-20	24-Jan-20	.
106248162U	A1000100068	27-Dec-19	3-Jan-20	.
106249382U	A1000100068	3-Jan-20	10-Jan-20	.
106252082U	A1000100068	19-Jan-20	26-Jan-20	.
106183082U	A1000100068	2-Jan-20	9-Jan-20	.
106248462U	A1000100205	28-Dec-19	4-Jan-20	28-Dec-19
106249462U	A1000100205	3-Jan-20	10-Jan-20	.
106252682U	A1000100205	19-Jan-20	26-Jan-20	.

Transaction table would have CUSTOMER_ID, TRAN_DT and TRAN_AMT (historical to t-1 date)

I could try with :

DATA _null_;

call symput('SD',"'"||(STRIP(put(today()-1,date9.)))||"'d");
call symput('SD12',"'"||(STRIP(put(intnx('Month',today(),-12,'S'),Date9.)))||"'d");

Run;

Proc sql;
create table Revenue as
select A.CUSTOMER_ID
, SUM(B.TRAN_AMT) AS REV_12M

from TEMP A
left join TRAN_TABLE B
on A.CUSTOMER_ID =B.CUSTOMER_ID
Where B.TRAN_DT GE &SD12. and B.TRAN_DT LE &SD.
Group by 1 ;
Quit;

Here issue is with 'SD' macro variable as it is changing for each customer so i am not able to think about the logic.

Rev_12M get calculated only for those customer who has shopped during that campaign period so customer *303 would have revenue only for one campaign which is 106248242U and same for customer A1000100052 revenue would be different as first time where clause window should be 01JAN2020 -365 days and for second time 03JAN2020 -365.

Kindly help.

Regards,

Uma Shanker Saini

Re: week calculation for indian financial calender

umashankersaini — Tue, 25 Jun 2019 18:31:56 GMT

Hi Team,

I am able to derive the desired output by following code:

Data _null_;
SD = intnx('week',today(),-1,'b')+1;
WK_ID=intck('week',intnx('month12.4',SD,0),SD)+1;
call symput('WK_ID',WK_ID);
Run;

Trying to automate macro code

umashankersaini — Tue, 25 Jun 2019 18:28:17 GMT

Hi Team,

Kindly help me to apply %if,%then and %else condition on following case.

Step 1 : Creating macro variables for dates

data _null_;
END_DT = intnx('month',today(),0,'e');
END_DT_2LST = intnx('month',today(),-1,'e');
END_DT_CURR = intnx('month',today(),1,'e');
call symput('END_DT', "'"||put(END_DT,date9.)||"'d");
call symput('END_DT_2LST', "'"||put(END_DT_2LST,date9.)||"'d");
call symput('END_DT_CURR', "'"||put(END_DT_CURR,date9.)||"'d");
run;

data test;
CURR_ED = intnx('month',&END_DT.,-1,'e');
CURR_SD1 = intnx('month',&END_DT.,-1,'b');
LST_ED = intnx('month',&END_DT.,-2,'e');
LST_SD24 = intnx('month',&END_DT.,-25,'b');
call symput('CURR_ED', "'"||put(CURR_ED,date9.)||"'d");
call symput('CURR_SD1', "'"||put(CURR_SD1,date9.)||"'d");
call symput('LST_ED', "'"||put(LST_ED,date9.)||"'d");
call symput('LST_SD24', "'"||put(LST_SD24,date9.)||"'d");
run;

data _null_;
call symput ("SCR_MNTH", compress(put((intnx('MONTH',&CURR_ED.,1,'e')),monyy5.)));
call symput ("LST_MNTH",compress(put((intnx('MONTH',&CURR_ED.,0,'e')),monyy5.)));
call symput ("LST2_MNTH",compress(put((intnx('MONTH',&CURR_ED.,-1,'e')),monyy5.)));
run;

%macro data_prep(END_DT=);

Proc sql;

create table Tran_&LST_MNTH._DV as

Select cust_id, flg_del from source

where DATE between &LST_SD24. and &LST_ED.; quit;

%if "&END_DT."="&END_DT." %then %do;

Data Tran_&LST_MNTH._DV;

set Tran_&LST_MNTH._DV;

run;

%end;

%if "&END_DT."="&END_DT_2LST." %then %do;

Data Tran_&LST2_MNTH._DV;

set Tran_&LST_MNTH._DV;

where flg_del=1;

run;

%end;

%if "&END_DT."="&END_DT_CURR." %then %do;

Data Tran_&SCR_MNTH._DV;

set Tran_&LST_MNTH._DV;

where flg_del=0;

run;

%end;

%mend data_prep;

%data_prep(END_DT=&END_DT.);;

%data_prep(END_DT=&END_DT_2LST.);

%data_prep(END_DT=&END_DT_CURR.);

i have just given you the approach, i am trying with it.

It's a huge repetitive code, running for consecutive three months. Only one step is different. Due to only that, code has been written three times. Kindly help me so that i can reduce the length of code.

week calculation for indian financial calender

umashankersaini — Thu, 06 Jun 2019 06:52:03 GMT

Hi Team,

I need to derive a week_id variable based on dates. As SAS starts calculating week from 01JAN but i want that should start with 01APR.

Looking for following result.

Date Week_id

01APR2019 1

13MAY2019 7

06JAN2020 42

following code is working fine till 31DEC2019 but for jan it is not working. Kindly help.

DATA A;

Week_id=week(date)-12;

run;

Re: PROC SQL Case when statement

umashankersaini — Sat, 01 Jun 2019 08:10:04 GMT

1. If customer is buying product online - then online only

2. If customer is buying by going to physical store - then store only

3. if customer is buying by both ways (online and store visit ) - Then unclassified

for online purchase - store_name would be same like 'ONLINE' across the globe

for physical store - Store_name would be any thing without pattern.

Re: PROC SQL Case when statement

umashankersaini — Sat, 01 Jun 2019 08:04:28 GMT

Hi Team,

Thanks for your quick reply.

There is no pattern for store_name, if offline (store visit). we have pattern for online purchase only.

I tried with :

proc sql;
Create table want (compress=yes) as
Select distinct CARD_NUM
, case when (STORE_NAME) like '%ONLINE%' then 'ONLINE ONLY'
when STORE_NAME not like '%ONLINE%' then 'STORE ONLY'
when STORE_NAME like '%ONLINE%' and STORE_NAME not like '%ONLINE%' then 'UNCLASSIFIED'
End as CHANNEL
From A
Group by 1;
quit;

This code is not working fine for unclassified category

PROC SQL Case when statement

umashankersaini — Fri, 31 May 2019 18:59:18 GMT

Hi Team,

I have PAN INDIA customer shopping table which contains online transaction details as well as store details.

I want to create a new variable for category like customer is online_only, Store_only and unclassified (if shopped by both criteria).

Sample dataset :

DATA C;
input CARD_NUM $5. STORE_NAME $7.;
CARDS;
12345 ONLINE
23456 STR_1
98765 ONLINE
98765 STR_NM
98765 STR_5
43214 STR_A
;
Run;

Desired output :

12345 ONLINE_ONLY
23456 STORE_ONLY
98765 UNCLASSIFIED
43214 STORE_ONLY

kindly note : For online transaction, i have only one indicator as 'ONLINE' but for store visit we have n number of store names.

Looking for your kind support.

Regards,

Uma Shanker Saini

Re: Hash join with multiple tables

umashankersaini — Thu, 13 Dec 2018 10:18:12 GMT

Hi,

I am looking for the output of following query :

Proc sql;
Create table ABC_SQL_INNER as
select A.name,A.Salary,B.Grade,B.Bin,C.Dept
from A
Inner join B
on A.Name=B.name
inner join C
on A.Name=C.name;
quit;

Proc sql;
Create table ABC_SQL_left as
select A.name,A.Salary,B.Grade,B.Bin,C.Dept
from A
left join B
on A.Name=B.name
left join C
on A.Name=C.name;
quit;

Proc sql;
Create table ABC_SQL_full as
select A.name,A.Salary,B.Grade,B.Bin,C.Dept
from A
full join B
on A.Name=B.name
full join C
on A.Name=C.name;
quit;

Hash join with multiple tables

umashankersaini — Thu, 13 Dec 2018 06:52:35 GMT

Hi Team,

Kindly let me know, how to write the hash join for multiple tables.

Joins could be inner join, Left join and full join.

DATA A;
input Name $1. Salary Age PIN;
Cards;
A 200 20 40
B 300 30 60
C 400 40 80
D 500 50 100
E 600 60 120
F 700 70 140
;
Run;

DATA B;
input Name $1. Salary Grade $2. BIN;
Cards;
A 200 AA 20
G 800 GG 80
H 900 HH 90
I 1000 II 100
F 700 FF 70
;
Run;

DATA C;
input Name $1. dept;
Cards;
A 111
B 222
C 333
Z 000
;
RUN;

Looking for your kind support as i am learning hash joins.

Regards,

Uma Shanker Saini

Re: Hash join Learning

umashankersaini — Wed, 12 Dec 2018 13:23:19 GMT

Hi,

I am so sorry for my delayed response.

During this time i tried with one more table for inner and left join but i was not able to do that.

Kindly help.

DATA C;
input Name $1. dept;
Cards;
A 111
B 222
C 333
Z 000
;
RUN;

Re: Hash join Learning

umashankersaini — Wed, 05 Dec 2018 14:01:29 GMT

what about inner join... I have tried hash for inner as well but in log... Table A has been read by 2 times..Can you please help me for inner join as well (optimized)

Re: Hash join Learning

umashankersaini — Wed, 05 Dec 2018 13:42:08 GMT

Great ... able to do that...

other important question ... what if i need to join more than two tables..

and any relevant doc to study Hash ...

Re: Hash join Learning

umashankersaini — Wed, 05 Dec 2018 12:52:56 GMT

yes.... All the variable has different name...No pattern and output includes both type like character (20 var) and Numeric (20 var).

Re: Hash join Learning

umashankersaini — Wed, 05 Dec 2018 11:22:28 GMT

Hi,

Thanks for your reply.

quick question.. Suppose i have 100 variables in B data set and want 40 variable from it in output.

Do i need to define them in below mentioned style:

defineData('Grade',GR1,TR1,AR20);

Hash join Learning

umashankersaini — Wed, 05 Dec 2018 07:05:26 GMT

Hi Team,

I am looking for same output from HASH join as i have from proc sql. Kindly help.

DATA A;
input Name $1. Salary Age;
Cards;
A 200 20
B 300 30
C 400 40
D 500 50
E 600 60
F 700 70
;
Run;

DATA B;
input Name $1. Salary Grade$2.;
Cards;
A 200 AA
G 800 GG
H 900 HH
I 1000 II
F 700 FF
;
Run;

Proc print;
run;

proc sql; create table AB_SQL as select A.*,B.Grade from A left join B on A.Name=B.Name; quit;

external converter unconvert failed

umashankersaini — Tue, 13 Nov 2018 11:28:37 GMT

Hi Team,

I am getting the following error while reading / fetching the records from SPDS table.

While searching on google everywhere its about compress option and parameters but code is already written and we are getting the error from yesterday only.

Error : external converter unconvert failed

Regards,

Uma Shanker Saini

Re: How to make NOT IN condition in SAS DI

umashankersaini — Thu, 12 Apr 2018 15:14:52 GMT

Hi Shmuel,

Thanks for your quick reply but have you read the question properly ??

If you are unable to understand the question then please let me know i could explain in more lay man language.

Regards,

Uma Shanker Saini

How to make NOT IN condition in SAS DI

umashankersaini — Thu, 12 Apr 2018 03:39:50 GMT

Hi Team,

Kindly let me know how to create sql not in sub query in SAS DI Studio.

For example we have table A ( with Saving A/C) and Table B ( Loan A/C with Saving A/C) and we want to create a table which have account numbers containing only saving A/C.

Table A	Table B	Table C
121	123	121
122	126	122
123	128	125
124	124	127
125	120	129
126
127
128
129
120

Query need to develop :

Create table C as Select * from A where ID not in ( select ID from B);

I have already tried with sql join transformation which includes sub-query in join condition with NOT IN but due to join (Inner join), It is providing Cartesian product.

Kindly suggest any other way or am i missing something.

Regards,

Uma Shanker Saini