Hi, i need to create new column previous_date for each acct_no, if current row is the first observation for particular acct_no then previous_date should be null, otherwise it should be the  observation(eff_date) from previous row, on the screenshot of results on previous_date, the value should be 21Jul2023 on row3 instead of null and row4 should be 30Jun2023              data have; input ACCT_NO EFF_DATE : date9. ; format EFF_DATE date9.; datalines; 1 21JUL2023 1 25JUL2023 2 30JUN2023 2 10JUL2023 2 01AUG2023 ; run; proc sort data=have; by ACCT_NO EFF_DATE; run; data want; set have; by ACCT_NO; if first.ACCT_NO then previous_date = .; else previous_date = lag(EFF_DATE); format previous_date date9.; run; ... View more

I below above data in sas ,so i need to create new columns ewallet_inbundle and cash_inbundle based on the below instructions  if payment_tpye=cash/ewallet for particular acct_no on first occurence of acct_no and service_fee=0 then default  value for both ewallet_inbundle and cash_inbundle should be 1, and also if payment_tpye=cash/ewallet for particular acct_no on first occurence  and service_fee>0 then value for both ewallet_inbundle and cash_inbundle should be 0 . From 2nd row to the last one of same acct_no, if payment_type=cash and service_fee>0 then cash_inbundle should be 0 but ewallet_inbundle status should be the one on previous row , also if payment_type=ewallet and service_fee>0 then ewallet_inbundle value should be 0(but retain previous status of cash_inbundle to previous one ..  Also 2nd row to the last one of same acct_no, if payment_type=cash and service_fee=0 then cash_inbundle should be 1 but ewallet_inbundle status should be the one on previous row , also if payment_type=ewallet and service_fee=0 then ewallet_inbundle value should be 1, but retain previous status of cash_inbundle to previous one .. See below dataset and my code, issue i am having is that on the output data( row 7 i get cash_inbundle value of 1 instead of 0 which was cash_inbundle value of previous row) data have; input acct_no date :date9. trns_amnt payment_type $ service_fee; format date date9.; datalines; 1 1-Oct-23 500 Ewallet 0 1 15-Oct-23 500 Ewallet 6 1 1-Nov-23 500 Ewallet 2.2 1 15-Nov-23 500 Ewallet 0 2 16-Nov-23 500 Ewallet 0 2 17-Nov-23 500 Cash 5 2 18-Nov-23 500 Ewallet 0 3 1-Dec-23 500 Cash 0 3 2-Dec-23 500 Cash 5.6 3 3-Dec-23 500 Cash 5.5 ; run; data want; set have; lagged_ewallet_inbundle = missing(lag(ewallet_inbundle)); lagged_cash_inbundle = missing(lag(cash_inbundle)); by acct_no; if first.acct_no then do; if service_fee = 0 then do; ewallet_inbundle = 1; cash_inbundle = 1; end; else do; ewallet_inbundle = 0; cash_inbundle = 0; end; end; else do; if payment_type = 'Cash' then do; if service_fee > 0 then do; cash_inbundle = 0; ewallet_inbundle = lagged_ewallet_inbundle; end; else do; cash_inbundle = 1; ewallet_inbundle = lagged_ewallet_inbundle; end; end; else if payment_type = 'Ewallet' then do; if service_fee > 0 then do; ewallet_inbundle = 0; cash_inbundle = lagged_cash_inbundle; end; else do; ewallet_inbundle = 1; cash_inbundle = lagged_cash_inbundle; end; end; end; drop lagged_ewallet_inbundle lagged_cash_inbundle; run; I have also attached output results ... View more

Good day, I have library called GEN which consists of monthly tables from 201801 to 202308 renamed as transact_base_201801 to transact_base_2023.   So, I need to append the tables from transact_base_201909 till transact_base_202308 using below code and I get the attached error. Kindly assist please  %macro easy_zero; data easy_zero_rtc; set GEN.TRANSACT_BASE_201909 - GEN.TRANSACT_BASE_202308; keep stamp sub_prod_cde SUB_SEGMENT Account_Type RTC_ONLINE_AMT RTC_BRNCH_AMT RTC_APP_AMT RTC_ONLINE_VOL RTC_BRNCH_VOL RTC_APP_VOL; where Account_Type ^='Credit Card' and sub_prod_cde='WX'; run; %mend easy_zero; %easy_zero;   ... View more

Hi I have two tables in sas,(acct_details table) with columns acct_no ,act_status and  yearmonth Table B( payment_trns table) has columns: yearmonth , trns_type,  acct_no , and eff_date   I need to fetch the first transaction from payment_trns(Only the trns_type, eff_date columns) done in the same period the account was re-activated. I created the logic on the provided code  below code but i get incorrect results on the screenshot below the code    data acct_details; format act_status $12.; input acct_no act_status $ yearmonth; datalines; 101 reactivated 202301 102 active 202301 103 reactivated 202302 103 reactivated 202303 ; run; data payment_trns; informat EFF_DATE date9.; format EFF_DATE date9.; input yearmonth trns_type $ acct_no eff_date; datalines; 202301 TypeA 101 05JAN2023 202301 TypeB 101 10JAN2023 202302 TypeC 101 15FEB2023 202301 TypeA 102 07JAN2023 202301 TypeB 102 12JAN2023 202302 TypeA 103 03FEB2023 202302 TypeB 103 10FEB2023 202303 TypeD 103 11MAR2023 ; run; proc sql; create table final_table as select A.*, B.eff_date, B.trns_type from acct_details as A left join ( select yearmonth, acct_no, min(eff_date) as first_eff_date from payment_trns group by yearmonth, acct_no ) as B_temp on A.yearmonth = B_temp.yearmonth and A.acct_no = B_temp.acct_no left join payment_trns as B on B_temp.yearmonth = B.yearmonth and B_temp.acct_no = B.acct_no where A.act_status = 'reactivated'; quit;     Data want act_status      acct_no      yearmonth  EFF_DATE     trns_type reactivated    101                202301        15JAN2023     TypeC reactivated   103                202302       03FEB2023     TypeA reactivated   103                202303      11MAR2023     TypeD ... View more

I have data i with columns:status(active or inactive),yearmonth(202301..etc) and acct_no so i need to execute below logic in sas: • StatusNew = Active if current yearmonth status=active, AND was also active in the previous month • Status New= Re-activated if current yearmonth status=active is  AND was inactive in the previous yearmonth. If account did not exist in the previous month, then just flag them as inactive or active based on their current month’s status.  Issue that i am having with my output from below code is that StatusNew for acct_no =2 in 202302 is Re-activated but it should be active based on above rules     data transactions; input yearmonth acct_no $ status $; datalines; 202301 1 inactive 202301 2 active 202302 1 inactive 202302 2 active 202302 3 inactive 202303 3 active 202302 4 active ; proc sort data=transactions; by acct_no yearmonth; run; data results; set transactions; by acct_no; length StatusNew $12; retain statusnew ' '; if first.acct_no then do; statusnew = status; end; else do; if lag(status)="active" and status = "active" then StatusNew = "Active"; else if lag(status)="inactive" and status = "active" then StatusNew = "Re-activated"; else StatusNew = status; end; if last.acct_no then output; drop lag_status; run; ... View more

Hi , i need to sum the amount of the first two transactions per cust_no on the below data                                                                       DATA HAVE  (TRNS_TABLE)  cust_no   prod_code     yearmonth      TRNS_AMT   TXN_TIME                      EFF_DATE   1                     WX                202212           500.00         9:09:40.000000          17DEC2022  1                      WX               202212           200.00        12:36:19.000000           19DEC2022  1                      WX               202301           500.00        11:14:00.000000           30JAN2023  1                      WX               202301           500.00        15:42:32.000000          30JAN2023  1                      WX               202301            450.00       12:18:57.000000            31JAN2023  2                      AB                202302           1000.00      12:22:45.00000             01FEB2023   The data is already orderd by eff_date and txn_time. The eff_date is date9. format, txn_time is time15.6 format. I need to sum first two transactions per cust_no and summarize my data per cust_no and yearmonth.                                                                   DATA WANT cust_no     yearmonth    sum_first_two 1                   202212                700 1                   202301               1000 2                  202302              1000     I tried below code didnt work data temp; set trns_table; by cust_no eff_date; retain rn; if first.cust_no then rn = 1; else rn + 1; run; proc sql; create table summary as select cust_no, yearmonth, sum( case when rn <= 2 then TRNS_AMT else 0 end ) as first_two_trns from temp group by cust_no, yearmonth; quit; ... View more