07-21-2024
yashpande
Obsidian | Level 7
Member since
07-24-2013
- 64 Posts
- 3 Likes Given
- 1 Solutions
- 1 Likes Received
-
Latest posts by yashpande
Subject Views Posted 1138 07-20-2024 02:48 PM 1179 07-20-2024 12:56 PM 634 06-20-2024 09:29 AM 474 06-19-2024 04:19 PM 750 06-13-2024 09:09 AM 905 06-06-2024 07:09 AM 455 06-24-2023 04:24 AM 633 10-29-2019 07:42 AM 1872 10-09-2019 09:48 AM 1909 10-09-2019 08:48 AM -
Activity Feed for yashpande
- Posted Re: Simplify proc sql multiple joins on SAS Programming. 07-20-2024 02:48 PM
- Posted Simplify proc sql multiple joins on SAS Programming. 07-20-2024 12:56 PM
- Posted multiple prxchange together on SAS Programming. 06-20-2024 09:29 AM
- Posted Values iteration for two datasets on SAS Programming. 06-19-2024 04:19 PM
- Posted Re: Generate Random values for encoding on SAS Programming. 06-13-2024 09:09 AM
- Posted Generate Random values for encoding on SAS Programming. 06-06-2024 07:09 AM
- Posted Need help in creating regular expression for prxchange feed on SAS Programming. 06-24-2023 04:24 AM
- Posted Modify taking lot of time on SAS Programming. 10-29-2019 07:42 AM
- Posted Re: Left Join or Merge of sas tables optimization on SAS Procedures. 10-09-2019 09:48 AM
- Posted Left Join or Merge of sas tables optimization on SAS Procedures. 10-09-2019 08:48 AM
- Posted Re: Datepart taking longer time on SAS Programming. 06-06-2019 11:04 PM
- Posted Datepart taking longer time on SAS Programming. 06-06-2019 02:43 PM
- Posted Swap and combination on SAS Programming. 04-25-2018 12:36 PM
- Got a Like for Increment datetime by 6hours. 03-23-2018 10:02 AM
- Posted Re: Increment datetime by 6hours on SAS Programming. 12-09-2017 02:25 AM
- Posted Increment datetime by 6hours on SAS Programming. 12-09-2017 12:24 AM
- Posted Re: Why Proc report produces only 1 record in output and Proc print produces all 10 observations on ODS and Base Reporting. 12-09-2017 12:08 AM
- Posted Re: Why Proc report produces only 1 record in output and Proc print produces all 10 observations on ODS and Base Reporting. 12-07-2017 11:50 AM
- Posted Why Proc report produces only 1 record in output and Proc print produces all 10 observations on ODS and Base Reporting. 12-07-2017 11:39 AM
- Posted Re: Proc SQL Union based on table on SAS Programming. 11-24-2017 10:26 PM
-
Posts I Liked
Subject Likes Author Latest Post 1 6 1 -
My Liked Posts
Subject Likes Posted 1 12-09-2017 12:24 AM
07-20-2024
02:48 PM
I need to build a SAS DI job using transformation for that . When I am combining all the tables in one SQL somehow the results are not matching and hence I need to make separate proc SQL for the same
... View more
07-20-2024
12:56 PM
Can somebody help me to break this one big proc sql into multiple proc sql as we need to convert it into SAS DI and I am somehow stuck and not getting the correct result because of the subquery
Any help is really appreciated. Thanks in advance
PROC SQL /* FULL QUERY in SAS LIB agreement snapshot*/ ;
CREATE TABLE im_rslts.CA_pr_betalen_basistabel_dly AS
SELECT distinct
(datepart(ARS.D0_CRD_RPT_DT)) AS periode_eenh_id FORMAT=DATE9.
, put(intnx('month', datepart(ARS.D0_CRD_RPT_DT), -1), yymmn6.) AS relatiecomplex
, ARS.D0_CRD_RPT_DT AS boekdag
, ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT AS saldo label="DEP_CLS_BOOK_BAL_AMT without D/C conversion (-1)"
, substr(ARD.NK_AR_NBR, 9, 10) AS rekening_nr
, ARD.HA_PD_CD AS product_type_cd
, (CASE
WHEN REL.relation_type="O" THEN "CLP"
WHEN REL.relation_type="P" THEN "RGB"
END) AS reftyp
, REL.relation_nr AS relnr
, CATX(' ', REL.person_name, REL.prefix_name, REL.initials) AS relnaam
, REL.segment_cd AS segment_cd
, REL.main_segment_group_cd AS main_segment_group_cd
, REL.grid_relation_nr AS grid_eenh_id
, REL.grid_segment_cdAS grid_cust_segm_code
, REL.gridsegm_eenh_id AS gridsegm_eenh_id
, REL.segm_eenh_id AS segm_eenh_id
, input(substr(REL.managing_entity,1,5),5.) AS kantrnr
, input(substr(REL.managing_entity,6,3),3.) AS relbehcd
, REL.econ_ultimate_grid_relation_nr AS grid_econ_ult_eenh_id
, ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT AS startsaldo LABEL="saldo van vorige dag"
, ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT AS eindsaldo LABEL="saldo van huidige dag"
, (ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT-COALESCE(ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT,0)) AS saldo_mutatie
,
(CASE
WHEN ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT NOT IN (. , 0)
THEN (ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT - ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT) / ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT * 100
WHEN ARS1.FM_ORIG_DEP_CLS_BOOK_BAL_AMT IN (. , 0)
THEN 100 * SIGN(ARS.FM_ORIG_DEP_CLS_BOOK_BAL_AMT)
END) AS percentage_saldo_wijziging
, S1.relatie_cd AS relatie_cd
, . AS afschrijving LABEL="TXN NOT PROMOTED"
, . AS bijschrijving LABEL="TXN NOT PROMOTED"
, . AS aanvullen LABEL="TXN NOT PROMOTED"
, . AS afromen LABEL="TXN NOT PROMOTED"
, ARD.HA_AHRD_OD_LMT_TP_CD AS limit_type_code
, ARS.FM_AHRD_OD_LMT_AMT AS authorized_overdraft_limit
, S2.limit_type_description AS limit_type_description
, (CASE
WHEN input(RA.bban_nr,10.) IN (SELECT NRC.rekening_nr FROM dsa_p_nl.SAV_V_NRC_REKENING_20240401 NRC) THEN 'J' ELSE 'N'
END) AS nrc_rekening_ind LABEL="sav_v_nrc"
, (ARS.FM_DEP_CLS_BOOK_BAL_AMT)*(ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND ="D") AS saldo_debet
, (ARS.FM_DEP_CLS_BOOK_BAL_AMT)*(ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND ="C") AS saldo_credit
, REL.affiliate_entity_type AS affiliate_entity_type
, REL.ma_segment_descr AS ma_segment_oms
, (CASE
WHEN REL.ma_segment_descr ='' THEN 'Onbekend'
WHEN REL.ma_segment_descr ='Financial Institutions' THEN 'FI'
WHEN REL.ma_segment_descr ='Midsized Corporates' THEN 'MC'
WHEN REL.ma_segment_descr ='Particulier Personal Banking' THEN 'Perba'
WHEN REL.ma_segment_descr ='Mass: Personal Banking' THEN 'Perba'
WHEN REL.ma_segment_descr ='Particulieren' THEN 'Mass'
WHEN REL.ma_segment_descr ='Mass' THEN 'Mass'
WHEN REL.ma_segment_descr ='Private Banking' THEN 'Priba'
WHEN REL.ma_segment_descr ='Product Clients' THEN 'PC'
WHEN REL.ma_segment_descr ='Retail - Other' THEN 'RO'
WHEN REL.ma_segment_descr ='Retail Companies' THEN 'SME'
WHEN REL.ma_segment_descr ='SME' THEN 'SME'
WHEN REL.ma_segment_descr ='Self-employed/Micro' THEN 'SEM'
WHEN REL.ma_segment_descr ='Wholesale - Other' THEN 'WO'
WHEN REL.ma_segment_descr ='Wholesale Corporates' THEN 'CC'
ELSE ma_segment_descr
END) AS ma_segment_naam
, (ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND = "C") AS aantal_credit
, (ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND = "D") AS aantal_debet
, (ARS.FM_AHRD_OD_LMT_AMT > 0)*(ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81')) AS aantal_limit
, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))
THEN MIN(ARS.FM_AHRD_OD_LMT_AMT, ((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT))
ELSE 0
END) AS saldo_debet_in_limit
, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))
THEN MAX(0, (((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT)-ARS.FM_AHRD_OD_LMT_AMT))
ELSE (ARS.FM_DEP_CLS_BOOK_BAL_AMT)*(ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND ="D")
END) AS saldo_debet_out_limit
, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))
THEN MIN(ARS.FM_AHRD_OD_LMT_AMT, ((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT))>0
ELSE 0
END) AS aantal_debet_in_limit
, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))
THEN MAX(0, (((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT)-ARS.FM_AHRD_OD_LMT_AMT))>0
ELSE 1
END) AS aantal_debet_out_limit
, (CASE WHEN (ARD.HA_AHRD_OD_LMT_TP_CD in ('D002', 'D010','D011','80','81'))
THEN ARS.FM_AHRD_OD_LMT_AMT-MIN(ARS.FM_AHRD_OD_LMT_AMT, ((ARS.DD_DEP_CLS_BOOK_BAL_DB_CR_IND="D")*ARS.FM_DEP_CLS_BOOK_BAL_AMT))
ELSE ARS.FM_AHRD_OD_LMT_AMT
END) AS dispositie_ruimte
FROM cl_exg.cl_exg_fp_ar_snpst_dly ARS
INNER JOIN cl_exg.cl_exg_d7_ar ARD ON ARS.d7_ar_id = ARD.s7_ar_id
LEFT JOIN (SELECT distinct ARS1.*, ARD1.s1_ar_id
FROM cl_exg.cl_exg_fp_ar_snpst_dly ARS1
INNER JOIN cl_exg.cl_exg_d7_ar ARD1 ON (ARS1.d7_ar_id = ARD1.s7_ar_id)
WHERE ((ARS1.d0_crd_rpt_dt = '31MAR2024:00:00:00'dt) AND (ARS1.d0_crd_rpt_dt between ARD1.TA_VLD_FROM_DT and ARD1.TA_VLD_TO_DT))
) ARS1 ON (ARD.s1_AR_ID = ARS1.s1_AR_ID)
LEFT JOIN dsa22.RELATION_AGREEMENT_202403 RA ON ((ARD.NK_AR_NBR = RA.iban_nr)
AND (RA.RELATION_NR = RA.main_relation_nr) AND (RA.mdm_product_category_cd=1))
LEFT JOIN dsa22.RELATION_202403 REL ON (RA.relation_nr = REL.relation_nr)
LEFT JOIN dsa_p_nl.stuur_pay_relatie_cd_202404 S1 ON (input(RA.bban_nr,10.) = S1.rekening_nr)
LEFT JOIN dsa_p_nl.STUUR_LIMIT_TYPE_CODE_202404 S2 ON ARD.HA_AHRD_OD_LMT_TP_CD = s2.limit_type_code
WHERE ARS.d0_crd_rpt_dt = '01APR2024:00:00:00'dt
AND ARD.HA_PD_GRP_CD = 'CRN_AC'
AND ARD.HA_PD_CD NOT IN ('4910', '4911', '4300') /*EXCLUDE FCA*/
/*AND ARS.D7_AR_ID = 1030000021058960*/
/*GROUP BY TXS.D0_CRD_RPT_DT, TXS.D7_AR_ID, TXS.D7_PD_ID, TXS.D7_TXN_CCY_ID*/
;
QUIT;
... View more
06-20-2024
09:29 AM
Is there a way I can use multiple prxchange in one go. i want to replace a with d, b with k, c with l, d with a ,
Here is my input
data a;
input_string="abcdjhj";
input_num=12;
input_string2= "robin";
run;
data b;
set a;
changed_string=prxchange('s/a/d/',-1,input_string);
changed_string=prxchange('s/b/k/',-1,input_string);
changed_string=prxchange('s/c/l/',-1,input_string);
changed_string=prxchange('s/d/a/',-1,input_string);
run;
as per logic i want changed_string value to be "ddklajhj" but this is not producing correct output and my table b has 16 such character variables i want to apply the same logic for those variables also. Can someone help me with the same
Thanks in advance. Any help is appreciated
... View more
06-19-2024
04:19 PM
I have an input dataset as below input_variables if a if b if c And another dataset with expression expression """= 10 then pk=100;" """= 20 then pk=300;" """= 30 then pk=500;" """= 40 then pk=600;" """= 50 then pk=800;" """= 60 then pk=900;" """= 70 then pk=1000;" """= 80 then pk=1300;" """= 90 then pk=1500;" """= 100 then pk=1800;" Now I want output as iteration of variable for all expression. Here is how it should look like "if a""= 10 then pk=100;" "if a""= 20 then pk=300;" "if a""= 30 then pk=500;" "if a""= 40 then pk=600;" "if a""= 50 then pk=800;" "if a""= 60 then pk=900;" "if a""= 70 then pk=1000;" "if a""= 80 then pk=1300;" "if a""= 90 then pk=1500;" "if a""= 100 then pk=1800;" "if b""= 10 then pk=100;" "if b""= 20 then pk=300;" "if b""= 30 then pk=500;" "if b""= 40 then pk=600;" "if b""= 50 then pk=800;" "if b""= 60 then pk=900;" "if b""= 70 then pk=1000;" "if b""= 80 then pk=1300;" "if b""= 90 then pk=1500;" "if b""= 100 then pk=1800;" "if c""= 10 then pk=100;" "if c""= 20 then pk=300;" "if c""= 30 then pk=500;" "if c""= 40 then pk=600;" "if c""= 50 then pk=800;" "if c""= 60 then pk=900;" "if c""= 70 then pk=1000;" "if c""= 80 then pk=1300;" "if c""= 90 then pk=1500;" "if c""= 100 then pk=1800;" Any help is really appreciated
... View more
06-13-2024
09:09 AM
This is very much on the lines of my expected output. Now I have got more clarity and sample. So here is my control table which has list of columns which needs to be encoded
Column
Type
person_name
Char
legal_name
Char
legal_name_GRIDORG
Char
date_of_birth
Num
prs_birtNowh_dt
Num
birth
Num
deceased_date
Num
deceased_notification_date
Num
prefix_name
Char
initials
Char
client_name
Char
legal_name_eup
Char
relation_nr
num
And here is the Sample data
relation_nr
relation_type
relation_start_date
relation_end_date
date_of_birth
established_date
deceased_date
deceased_notification_date
person_name
prefix_name
initials
segment_cd
918598
P
11/4/1994
.
9/7/1972
.
.
.
Nolte
B
390
918814
P
11/4/1994
.
3/4/1924
.
6/6/2019
########
Bruggen
van der
A
390
918832
P
11/4/1994
.
11/20/1926
.
.
.
Griffioen
J
390
918857
P
11/4/1994
.
5/3/1929
.
########
########
Kneppers
H
390
918923
P
11/4/1994
.
1/29/1932
.
########
5/8/2018
Noort
van
J
390
918997
P
11/4/1994
.
7/17/1914
.
4/7/2008
########
Heijermans
L
390
919224
P
11/4/1994
.
12/15/1947
.
########
########
Schaap
R
390
919222
P
11/4/1994
.
3/16/1951
.
.
.
Beleir
de
APM
390
919271
P
11/4/1994
.
6/28/1961
.
.
.
Kolk
van der
CJ
390
919136
P
11/4/1994
.
5/30/1975
.
.
.
Schoorl
HJ
390
So How do I generate the encoding and if required how can I decode it ? Any further help is highly appreciated
... View more
06-06-2024
07:09 AM
I have list of personal columns which needs to be masked/encoded/encrypted so sensitive information is not passed to all the users. Column list is customer_name,customer_id, customer_dob So everytime we download data from production ,all these columns would have different value which are not traceable. Input data set looks like this customer_name customer_id customer_dob balance Rahul 1000 29/04/2002 10058 Ramesh 1005 12/03/1998 15008 So my output can be something like this customer_name customer_id customer_dob balance Yaswl 5423 17/11/1992 10058 Oweqyu 6421 12/03/1998 15008 Any suggestions is highly appreciated
... View more
06-24-2023
04:24 AM
Hi, I have below values in datset Or OR- A OR B AND+ and Or- ELSE 1ELSE WHAT I want is out put as below OR- AND+ Or- 1ELSE So Basically if there is any digit or expression just immediately after SAS reserved keywords then it should be selected and this needs to be done using Prxchange regular expression as then it would be feed off to another third-party tool hence a prxchange regex is must I am completely out of ideas as I haven't used regex before Any help is really appreciated
... View more
10-29-2019
07:42 AM
Hi All,
Basically we have table loader code from SAS DI which looks as below. We are using Update Insert technique in DI and hence below is the code which is used. However, it is taking whole lot of time. sasdata is sas library. And we have indexes on both the tables.
sasdata.TBL_DTLS_ERROR_APD has 212354 records and etls_lastTable has 60000 records.
I am out of all my ideas on how to reduce this time of execution. Any help is really appreciated.
data sasdata.TBL_DTLS_ERROR_APD; length etls_msg $200; drop etls_msg; modify sasdata.TBL_DTLS_ERROR_APD &etls_lastTable updatemode = nomissingcheck ; by POL_NUM_TXT INSURED_ID; /* if the record does not exist in the master, then add it */ if %sysrc(_DSENMR) eq _iorc_ then output; /* if the record exists in the master, then replace it */ else if %sysrc(_SOK) eq _iorc_ then replace; %if &etls_StopOnIORC ne %then %do; if _iorc_ in (&etls_StopOnIORC) then do; etls_msg = iorcmsg(); put etls_msg; %rcSetDS(5) stop; end; %end; _iorc_ = 0; _error_ = 0; run;
... View more
10-09-2019
09:48 AM
I want 36 columns from Agent table and 18 columns from B table. Length of POLICY_NUMBER is 200.
... View more
10-09-2019
08:48 AM
Hi,
I have below query which is taking lot of time
proc sql; create table agent1 as select a.*, B.DEAL_BRANCH_CITY, B.DEAL_BRANCH_KEY, B.DEAL_BRANCH_NAME, B.DEAL_BRANCH_STATE, B.DEAL_PRIMARY_SUBVERTICAL_KEY, B.DEAL_PRIMARY_VERTICAL_KEY, B.DEAL_PRI_SSUB_VERTICAL_DESC, B.DEAL_PRI_VVERTICAL_DESC, B.DEAL_RM_CODE, B.DEAL_RM_NAME, B.DEAL_SEC_RM_CODE, B.DEAL_SEC_RM_NAME, B.DEAL_SEC_SSUB_VERTICAL_DESC, B.DEAL_SEC_SUBVERTICAL_KEY, B.DEAL_SEC_VERTICAL_KEY, B.DEAL_SEC_VVERTICAL_DESC, B.POL_DEAL_DSA_NAME, B.POL_DEAL_NUM FROM work.agent as a LEFT JOIN STAGE.master_LOOKUP AS B ON UPCASE(A.POLICY_NUMBER)=UPCASE(B.POL_NUM) ; quit;
Here, Agent table has 8731382 records and no duplicates It has 36 variables. My B table is pain it has 167171496 and has duplicates on the key column and has total 29 columns. Both of these tables are SAS tables and have indexes on respective columns. I tried with HASH join but it still takes huge time.
How can I reduce the execution time for this ? Any suggestion is highly appreciated
... View more
06-06-2019
02:43 PM
Hi All, I have oracle table which has date time variable and I need to create sas table with date9 format. Oracle table has more than 30million records. It takes lot of time to extract data due to conversion. Any help in optimization would be really great help Below is the sample code Proc sql; Create table t1 as Select a.* , b.reporting_date format=date9. from have left join oracle_lib b On a. Id=b.id where datepart(b.Reporting_date) =a. Eff_from_date; Quit; In another code reporting _date has value like 06Jun2019:00:00:00 Eff_from_date has values like 06-Jun-2019 Any help in reducing the time is of really good help.
... View more
04-25-2018
12:36 PM
Hi all, I have below data. I need to flag observations based on combination. Below is the sample data Data have; Input account_id 8. Credit_amount 8. debit_amount 8.; Cards; 1 10 100 2 20 200 1 100 10 3 20 200 4 10 100 2 200 20 5 10 50 ; Run; So if you look at observation 1 and 3 Debit and credit are interchanged and hence this transaction needs to be flagged. Same is the case with observations 2 and 7 hence this also needs to be flagged. So I want output as below ACCOUNT_ID CREDIT_AMOUNT DEBIT_AMOUNT FLAG 1 10 100 Y 2 20 200 Y 1 100 10 Y 3 20 200 N 4 10 100 N 2 200 20 Y 5 10 50 N I tried using concatenation, however look back is something which is I am not able to figure out. Any help is really appreciated. Thanks in advance
... View more
12-09-2017
02:25 AM
Thanks for the clue. Worked like a charm. Just in case if anyone needs the code.
data have; input dsstartdate dsenddate; cards; 1827705600 1861920000 ; run;
data want; set have; format dsstartdate dsenddate datetime25.6; do dt = DSSTARTDATE to DSENDDATE by '06:00:00't; if timepart(dt)=0 then counter=1; if timepart(dt)=21600 then counter=2; if timepart(dt)=43200 then counter=3; if timepart(dt)=64800 then counter=4; format dt datetime25.6; output; end;
keep dt counter; run;
... View more
12-09-2017
12:24 AM
1 Like
Hi There,
I want to prepare dataset which has values for every 6 hours.
data new; input loop dsstartdate ; datalines; 1 1827705600 ;
run;
data new2; set new;
format dsstartdate datetime25.6; run;
so my data looks like this
Loop DSSTARTDATE
1 01DEC2017:00:00:00.000000
And this is how I want
1
01DEC2017:00:00:00.000000
2
01DEC2017:06:00:00.000000
3
01DEC2017:12:00:00.000000
4
01DEC2017:18:00:00.000000
1
02DEC2017:00:00:00.000000
2
02DEC2017:06:00:00.000000
3
02DEC2017:12:00:00.000000
4
02DEC2017:18:00:00.000000
1
03DEC2017:00:00:00.000000
2
03DEC2017:06:00:00.000000
3
03DEC2017:12:00:00.000000
4
03DEC2017:18:00:00.000000
So Basically A loop through all the dates by every 6 hours. I was using intnx in a do loop
However somehow cant be able to crack it.
Anyhelp is appreaciated
... View more