Update values in same table based on most recent values

Swapnil_21 — Sun, 09 Aug 2020 14:31:50 GMT

Hi , I need to update the observations in a table based on most recent. Below is the sample of what I have in table.

Original_A_GSTIN	Original_A_RET_PERIOD	Original_A_B2BI_CTIN	Original_A_RET_DATE	Original_A_B2BI_IDT	Original_A_B2BI_VAL	Amended_A_B2BI_VAL	Original_A_B2BI_NUM	Amended_A_B2BI_NUM	Original_A_B2BI_RT	Amended_A_B2BI_RT	Original_A_B2BI_TXVAL	Amended_A_B2BI_TXVAL	Original_A_B2BI_IAMT	Amended_A_B2BI_IAMT	Original_A_B2BI_CAMT	Amended_A_B2BI_CAMT	Original_A_B2BI_SAMT	Amended_A_B2BI_SAMT	Original_A_B2BI_CSAMT	Amended_A_B2BI_CSAMT	Original_A_B2BI_DIFF_PERCENT	Amended_A_B2BI_DIFF_PERCENT	Amended_A_B2BI_SECTION	Original_A_B2BI_SECTION	Amended_A_RET_PERIOD
27AAAAA0042K1Z7	122017	07AHYPA1487G1Z2	16Feb2018	02Nov2017	117528.00	117528.00	1801	1801	18.00	18.00	99600.00	99600.00	0.00	17928.00	8964.00	0.00	8964.00	0.00	0.00	0.00	0.00	0.00	9A	4A	032019
27AAAAA0042K1Z7	122017	07AHYPA1487G1Z2	16Feb2018	02Nov2017	84960.00	84960.00	1801	1801	18.00	18.00	72000.00	72000.00	0.00	12960.00	6480.00	0.00	6480.00	0.00	0.00	0.00	0.00	0.00	9A	4A	032019
27AAAAA0042K1Z7	032018	27AIEPR1584Q1ZH	01May2018	04Jan2018	20650.00	20650.00	1801	1801	18.00	18.00	17500.00	17500.00	0.00	3150.00	1575.00	0.00	1575.00	0.00	0.00	0.00	0.00	0.00	9A	4A	032019
27AAAAA0042K1Z7	032018	27AIEPR1584Q1ZH	01May2018	12Jan2018	4130.00	8260.00	1801	1801	18.00	18.00	3500.00	7000.00	0.00	0.00	315.00	630.00	315.00	630.00	0.00	0.00	0.00	0.00	9A	4A	032019

My group is based on Original_A_GSTIN, Original_A_RET_PERIOD,Original_A_B2BI_CTIN

and Original_A_B2BI_IDT so if all of these matches.( E.g. First two observations ) then I need to update the values of

Original_A_B2BI_VAL = Amended_A_B2BI_VAL
Original_A_B2BI_NUM = Amended_A_B2BI_NUM
Original_A_B2BI_RT= Amended_A_B2BI_RT
Original_A_B2BI_TXVAL = Amended_A_B2BI_TXVAL
Original_A_B2BI_IAMT = Amended_A_B2BI_IAMT
Original_A_B2BI_CAMT = Amended_A_B2BI_CAMT
Original_A_B2BI_SAMT = Amended_A_B2BI_SAMT
Original_A_B2BI_CSAMT = Amended_A_B2BI_CSAMT
Original_A_B2BI_DIFF_PERCENT = Amended_A_B2BI_DIFF_PERCENT

And then remove the last record (Basically replace original records with last.record). This is how I want the output

Original_A_GSTIN	Original_A_RET_PERIOD	Original_A_B2BI_CTIN	Original_A_RET_DATE	Original_A_B2BI_IDT	Original_A_B2BI_VAL	Amended_A_B2BI_VAL	Original_A_B2BI_NUM	Amended_A_B2BI_NUM	Original_A_B2BI_RT	Amended_A_B2BI_RT	Original_A_B2BI_TXVAL	Amended_A_B2BI_TXVAL	Original_A_B2BI_IAMT	Amended_A_B2BI_IAMT	Original_A_B2BI_CAMT	Amended_A_B2BI_CAMT	Original_A_B2BI_SAMT	Amended_A_B2BI_SAMT	Original_A_B2BI_CSAMT	Amended_A_B2BI_CSAMT	Original_A_B2BI_DIFF_PERCENT	Amended_A_B2BI_DIFF_PERCENT	Amended_A_B2BI_SECTION	Original_A_B2BI_SECTION	Amended_A_RET_PERIOD
27AAAAA0042K1Z7	122017	07AHYPA1487G1Z2	16-Feb-18	02-Nov-17	84960	84960	1801	1801	18	18	72000	72000	0	12960	6480	0	6480	0	0	0	0	0	9A	4A	32019
27AAAAA0042K1Z7	32018	27AIEPR1584Q1ZH	01-May-18	04-Jan-18	20650	20650	1801	1801	18	18	17500	17500	0	3150	1575	0	1575	0	0	0	0	0	9A	4A	32019

I have tried sorting using these 4 variables but somehow I am unable to get combination together. Any help is really appreciated.

Re: Update values in same table based on most recent values

Shmuel — Sun, 09 Aug 2020 14:04:46 GMT

According to your definition Original_A_B2BI_CTIN should fit in both rows,

so is it a typo showing different values on line 1 and 2 ?

Re: Update values in same table based on most recent values

Swapnil_21 — Sun, 09 Aug 2020 14:13:14 GMT

Yes sir ..its a typeo

Re: Update values in same table based on most recent values

Shmuel — Sun, 09 Aug 2020 14:32:59 GMT

@Swapnil_21 wrote:
Yes sir ..its a typeo

I have edited your original post accordingly with hope to deal with it later.

Re: Update values in same table based on most recent values

Shmuel — Sun, 09 Aug 2020 16:58:25 GMT

Comparing your wanted results to the input data, there is no any UPDATE of data, but

just keeping the last row of a group. Check results of next code:

data want;
 set have;
  by Original_A_GSTIN   Original_A_RET_PERIOD 
       Original_A_B2BI_CTIN  Original_A_B2BI_IDT;
  if last.Original_A_B2BI_IDT;
run;

topic Re: Update values in same table based on most recent values in SAS Programming

Update values in same table based on most recent values

Re: Update values in same table based on most recent values

Re: Update values in same table based on most recent values

Re: Update values in same table based on most recent values

Re: Update values in same table based on most recent values