About rahulsaha2127

rahulsaha2127 · ‎02-22-2023

I have to convert this code to Pyspark. Since I am unable to do that I was trying to convert it to sql, and then pass the sql query in Pyspark

rahulsaha2127 · ‎02-22-2023

I have this SAS code using first. and recursive if else loops which I need to convert to an SQL query. But I am not able to understand how to do it because of the retain statement and calling numerous variables in multiple loops. Can someone please help me to convert it to SQL query? data new_data; set raw_data(where=(appr_date ne .)); by loan_number period_dt; retain prev_appr_date prev_upd_appr_date appraisal_cross; if first.loan_number then do; if appr_date > period_dt then do; prev_appr_date=appr_date; upd_appr_date=period_dt; prev_upd_appr_date=upd_appr_date; appraisal_cross=1; x=1; end; else if appr_date <= period_dt then do; prev_appr_date=appr_date; upd_appr_date=appr_date; prev_upd_appr_date=appr_date; appraisal_cross=0; x=2; end; end; else do; if appr_date<prev_upd_appr_date and appr_date ne prev_appr_date then do; upd_appr_date=max(period_dt,prev_upd_appr_date); appraisal_cross=1; x=3; end; else if appr_date=prev_appr_date and appraisal_cross=1 then do; upd_appr_date=prev_upd_appr_date; appraisal_cross=1; x=4; end; else if appr_date>=prev_upd_appr_date and appr_date>period_dt then do; upd_appr_date=period_dt; appraisal_cross=1; x=5; end; else if appr_date>=prev_upd_appr_date and appr_date <=period_dt then do; upd_appr_date=appr_date; appraisal_cross=0; x=6; end; prev_upd_appr_date=upd_appr_date; prev_appr_date=appr_date; end; run; Thank you in advance. Hope to get some help on this.

rahulsaha2127 · ‎02-13-2023

I have the following code which I need to convert in SAS form: create volatile table gl as ( select GEN_ID, AU_GEN_ID, TYPE_2_DESC, TYPE_5_DESC, TYPE_8_DESC from COMMERCIAL_V3.DATA_F qualify row_number() over (partition by , GEN_ID, AU_GEN_ID order by DATA_OUTSTANDING_AMT desc, DATA_TOTAL_COMMITMENT_AMT desc, DATA_GEN_ID desc)=1 ) with data on commit preserve rows; Please share your inputs. TIA.

rahulsaha2127 · ‎01-22-2023

I have the following code which I need to decode and will require the communities help to understand if my thinking is correct. Here's the code: data facilities(index=(obk fack)) f_xclud ; format arrival_dt_system last_dt date9.; length cd_base_id $6. cm_ind_l2 $25.; if _n_ eq 1 then do; declare hash nai (dataset:"D_industry"); nai.definekey ('ind_gen_id'); nai.definedata ('cd_base_id','cm_ind_l2'); nai.definedone (); call missing(cd_base_id, cm_ind_l2); declare hash arv (dataset:"f_a_dt"); arv.definekey ('ps_o_id', 'ps_s_id ', 'ps_f_id'); arv.definedata ('arrival_dt_system', 'last_dt'); arv.definedone (); call missing(arrival_dt_system, last_dt); end; merge facs_sorted(in=from1) f_lg_v(in=from2) f_xclud(in=from3) defaults(in=from4) end = last_obs; by ps_o_id ps_s_id ps_f_id period_dt; rc = arv.find(); rc = nai.find(); drop rc; if pd_e_flag EQ 'Y' then output f_xclud; else output facilities; run; My understanding: Its Creating a merged dataset from the 4 tables: facs_sorted(in=from1) f_lg_v(in=from2) f_xclud(in=from3) defaults(in=from4) usinf the variables mentioned after "BY" Then its left joining the merged table with the dataset mentioned under the two hash object with their respective keys. Finally its storing the output table based on the variable pd_e_flag and its value, Please let me know if I am missing anything. TIA

rahulsaha2127 · ‎01-20-2023

After the join using the hash table for which you have provided the sql alternative, what is the code exactly trying to do? Is it creating new variables on the newly created dataset tem_excludes? Am referring to this part: if secure_exclusion then p_flag= 'Y'; if count EQ 1 then do; p_flag= 'Y'; single_entry = 1; end; else single_entry = 0; run;

rahulsaha2127 · ‎01-19-2023

I have a code leveraging SAS Hash Tables for join and I want to create the same table using Procedure SQL. Here is the following HASH Table code: data tem_excludes; if _n_ eq 1 then do; declare hash sec (dataset:"d_sec_list"); sec.definekey ('curr_obr_num', 'sys', 'fc_num'); sec.definedata ('secure_exclusion'); sec.definedone (); call missing(secure_exclusion); declare hash sng (dataset:"single_entry"); sng.definekey ('fackey'); sng.definedata ('count'); sng.definedone (); call missing(count); end; set tem_exclude0; rc = sec.find(); rc = sng.find(); if secure_exclusion then p_flag= 'Y'; if count EQ 1 then do; p_flag= 'Y'; single_entry = 1; end; else single_entry = 0; run; Would need to write an equivalent code using Proc sql without any hash table. Please share your inputs.

rahulsaha2127 · ‎11-30-2022

I have the following dataset: PERIOD_DT volume fee rate 1-Mar-19 24763264905 22970651.85 0.00092761 1-Jun-19 24934381664 23863270.19 0.000957043 1-Sep-19 25524066533 24138580.04 0.000945718 1-Dec-19 26252530371 24788811.26 0.000944245 1-Mar-20 22489080601 24778303.2 0.001101793 1-Jun-20 24536686716 23310005.11 0.000950006 1-Sep-20 26462538909 27042949.4 0.001021933 1-Dec-20 25281487445 28543895.47 0.001129043 1-Mar-21 33741326514 32391701.02 0.000960001 1-Jun-21 32301592660 31960263.68 0.000989433 1-Sep-21 34204484286 37244115.76 0.001088867 1-Dec-21 37037612470 35116904.14 0.000948142 1-Mar-22 41882893049 29976825.33 0.00071573 1-Jun-22 37553692649 32769017.55 0.000872591 1-Sep-22 38841056977 32133320.61 0.000827303 I have the following equation: pred_rate=rate_lag1 -0.68944167689726*rate_diff_lag1-0.4791311284*rate_diff_lag2; where : rate= volume/fee; rate_lag1 = lag(rate); rate_lag2=lag2(rate); rate_diff = rate - rate_lag1; rate_diff_lag1 = lag(rate_diff); rate_diff_lag2=lag2(rate_diff); Now I want to create pred_rate column(rate prediction) with the mentioned formula but I will need to replace actual rate with the predicted rate for all prediction after sep'20. For eg: For sep'20 we will use the last quarter (jun'20) rate for making the prediction i.e pred_rate. From the following quarter which is Dec'20 we will use the pred_rate value of Last quarter (Sep'20) to make the calculations and use it in the pred_rate formula for calculating predictions in Dec'20 and so on. Please provide your valuable inputs. TIA.

rahulsaha2127 · ‎11-30-2022

I have a SAS code which will refer to a formula stored in external text file. input data: x y 10 67 15 70 30 71 output data: x y z 10 67 77 15 70 85 30 71 101 Written SAS Code: data want; set have; %include "path/file_ln_model.txt"; run; Text file (file_ln_model.txt): z= x+y; This above sas code throws the following error: 27 +z=x+y; _____________ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. Please help to find the error in this code and provide any alternative situations. Thanks in advance.

rahulsaha2127 · ‎11-18-2022

What IF I want to include more quarters and not only 'Q4'? I might want to have Q5, Q6 as well for suppose. What changes need to be done ? Also Thanks for your response!!

rahulsaha2127 · ‎09-14-2022

I have a data with Quarter , Product and SellAmount. I want to take cumulative sum of sell amount grouped by product and quarter. My data is like this: Quarter Product Sell_Amount Q1 P1 220.875 Q1 P2 29.3746 Q1 P3 288.526 Q1 P4 95 Q2 P1 204.85 Q2 P2 235 Q2 P3 820.1037 Q3 P2 583.363 Q3 P3 579.1 Q3 P4 1110.8 There are 3 quarters in data but I want a grouped table where I want to include quarter 4 as well and force-feed the PRODUCT sell_amount in Quarter 4 equal to ZERO so that the cumulative sum for product sell amount in Q4 is equal to the last quarter (Quarter 3). There can be multiple force-feed quarters like this (Q5,Q6) which doesnot exist in the data l but will be needed for reporting in final pivot and required output (Cumulative sell amount) will take the same value as the last available quarter for these quarters. Also not all products are sold in every quarter so I want o adjust my cumulative sum accordingly. I need a table like this: Product Quarter Sell_Amount Cumulative_sum P1 Q1 220.875 220.875 P1 Q2 204.85 425.725 P1 Q3 0 425.725 P1 Q4 0 425.725 P2 Q1 29.3746 29.3746 P2 Q2 235 264.3746 P2 Q3 583.363 847.7376 P2 Q4 0 847.7376 P3 Q1 288.526 288.526 P3 Q2 820.1037 1108.63 P3 Q3 579.1 1687.73 P3 Q4 0 1687.73 P4 Q1 95 95 P4 Q2 0 95 P4 Q3 1110.8 1205.8 P4 Q4 0 1205.8 As highlighted by red in this table there is no data for Q4 but I want to forcefeed information so that all product sell in Q4 equals to zero and the cumulative sum remains the same in Q4 as in Q3. Also there might be some products with no sell in a given quarter but we want to include that product record for the quarter with sell_amount=0 and accordingly create the cumulative sum variable. Please help with your answers!

rahulsaha2127 · ‎10-05-2021

Hi, I have the following data: User Date Transaction Amount A 10-Aug-21 120 A 11-Aug-21 110 A 15-Aug-21 600 A 20-Aug-21 585 A 21-Aug-21 130 B 7-Aug-21 300 B 8-Aug-21 350 B 8-Aug-21 1450 B 9-Aug-21 10000 I want to flag those users who does a transaction with value greater than 5 times the average of all the last transactions and remove these anomalous transactions from the average calculation in the next step. So my table would look like: User Date Transaction Amount Average Flag A 10-Aug-21 120 0 0 A 11-Aug-21 110 120 0 A 15-Aug-21 600 115 1 A 20-Aug-21 585 115 1 A 21-Aug-21 130 115 0 B 7-Aug-21 300 0 0 B 8-Aug-21 350 300 0 B 8-Aug-21 1450 325 0 B 9-Aug-21 11000 2100 1 So here, For each transaction of an user,we need to look at the average of the last transactions and check if the current transaction amount > 5 times the average amount. If yes, then flag variable will take the value 1. Now, When we see such a case we will not include that particular transaction for average calculation in the next step, so that we don't miss the next anomalous transaction due to the increase in the average of the last transactions. Appreciate any suggestions!

Online Status	Offline
Date Last Visited	‎06-06-2023 04:54 PM

Re: Convert SAS Data step to SQL query

Convert SAS Data step to SQL query

Convert SQL / teradata code to SAS

Decode Hash Table Code

Re: Convert Hash Table code to SQL in SAS

Convert Hash Table code to SQL in SAS

Use last predicted value for future forecasting

Read code from Text file as part of sas code

Re: HOW TO GROUP BY EVEN FOR THE SEGMENTS WITH NULL VALUES

HOW TO GROUP BY EVEN FOR THE SEGMENTS WITH NULL VALUES

Re: Read code from Text file as part of sas code

Re: HOW TO GROUP BY EVEN FOR THE SEGMENTS WITH NULL VALUES

Re: HOW TO GROUP BY EVEN FOR THE SEGMENTS WITH NULL VALUES

Re: Compare current amount with average transaction amount based on th...

Compare current amount with average transaction amount based on the la...

Re: Convert SAS Data step to SQL query

Convert SAS Data step to SQL query

Convert SQL / teradata code to SAS

Decode Hash Table Code

Re: Convert Hash Table code to SQL in SAS

Convert Hash Table code to SQL in SAS

Use last predicted value for future forecasting

Read code from Text file as part of sas code

Re: HOW TO GROUP BY EVEN FOR THE SEGMENTS WITH NULL VALUES

HOW TO GROUP BY EVEN FOR THE SEGMENTS WITH NULL VALUES

Compare current amount with average transaction amount based on the la...