topic Re: Insert new rows into data based on existing rows in SAS Programming

Insert new rows into data based on existing rows

A_Swoosh — Fri, 05 Nov 2021 01:09:17 GMT

Hi SAS community,

I'm trying to insert additional rows into my data based on existing data:

data have1;
  infile cards truncover expandtabs;
  input STORE_ID $ transaction_date:date9. last_transaction_dt:date9. open_date:date9. close_date:date9.;
  format transaction_date last_transaction_dt open_date close_date date9.;
  cards;
A0001 06sep2013 04apr2011 01jan2011 19dec2016  
A0001 15dec2015 26oct2013 01jan2011 19dec2016  
;

I want to create a new table that adds an extra row:

data want;
  infile cards truncover expandtabs;
  input STORE_ID $ open_date :date9. close_date :date9. last_transaction_dt :date9. transaction_date :date9.;
  format transaction_date last_transaction_dt open_date close_date date9.;
  cards;
A0001 01jan2011 04apr2011 .         04apr2011  
A0001 06sep2013 26oct2013 04apr2011 06sep2013  
A0001 15dec2015 19dec2016 26oct2013 15dec2015  
;

Basically, using a combination of the open_date and last_transaction_dt, I want to create a new row at the top to indicate the open information leading up to the last transaction before my gap to September 6, 2013. Any direction would be appreciated.

Thanks,

Re: Insert new rows into data based on existing rows

ballardw — Fri, 05 Nov 2021 08:50:19 GMT

Since your example Want data does not have the second and third observations exactly the same as in the Have1 data then you are doing considerably more than inserting a row. So we would need to see the rules involved for modifying the Close_date, Last_transaction_date and Transaction_date variables.

@A_Swoosh wrote:

Hi SAS community,

I'm trying to insert additional rows into my data based on existing data:
data have1;
  infile cards truncover expandtabs;
  input STORE_ID $ transaction_date:date9. last_transaction_dt:date9. open_date:date9. close_date:date9.;
  format transaction_date last_transaction_dt open_date close_date date9.;
  cards;
A0001 06sep2013 04apr2011 01jan2011 19dec2016  
A0001 15dec2015 26oct2013 01jan2011 19dec2016  
;
I want to create a new table that adds an extra row:
data want;
  infile cards truncover expandtabs;
  input STORE_ID $ open_date :date9. close_date :date9. last_transaction_dt :date9. transaction_date :date9.;
  format transaction_date last_transaction_dt open_date close_date date9.;
  cards;
A0001 01jan2011 04apr2011 .         04apr2011  
A0001 06sep2013 26oct2013 04apr2011 06sep2013  
A0001 15dec2015 19dec2016 26oct2013 15dec2015  
;
Basically, using a combination of the open_date and last_transaction_dt, I want to create a new row at the top to indicate the open information leading up to the last transaction before my gap to September 6, 2013. Any direction would be appreciated.

Thanks,

Re: Insert new rows into data based on existing rows

A_Swoosh — Sun, 07 Nov 2021 20:35:59 GMT

You're right. I need to go back to the original data and work from that.

I want to basically keep, for each store_id, the first and last row in addition to any rows where the transaction_date is > 30 days from the last transaction_date.
- I want to essentially create an open and close period for each store and treat gaps of 30 days as a closure.
From there, I want to define open and close dates based on the transaction_dates between the rows where they have gaps which I define as closed.

data have1;
  infile cards truncover expandtabs;
  input STORE_ID $ customer_id $ transaction_id $ transaction_date :date9.;
  format transaction_date date8.;
  cards;
A0001 aa 1 22jan2010  
A0001 aa 2 23jan2010  
A0001 ay 3 24jan2010  
A0001 ab 4 11mar2010  
A0001 cc 5 11jun2011  
A0001 dd 6 12jun2012  
A0001 ee 7 13jul2012  
A0001 ee 8 10aug2012  
A0001 ef 9 10aug2012  
A0001 ff 10 10aug2012  
A0001 ff 11 07dec2012  
A0001 gg 12 22dec2012  
A0001 ds 13 01jan2013  
A0002 fz 1 07dec2012  
A0002 gb 2 22dec2012  
A0002 dw 3 01jan2013  
A0003 mg 1 22dec2012  
A0003 sf 2 01jan2013  
;

data want;
  set have1;
  by store_id;
  difdate=ifn(first.store_id,.,dif(transaction_date));
  if first.store_id or transaction_date>cutoff then do;
    cutoff=transaction_date+30;
  end;
 last_transaction=lag(transaction_date);
if first.store_id then last_transaction=.;
if last.store_id then do;
if transaction_date le '31-DEC-2012'd then closed=1; 
end;
if difdate gt 30 then closed=1;
  retain cutoff;
  format cutoff last_transaction date9.;
 run;