How to add rows

[Tiri6]

Hi,

i need to modify my input dataset creating new rows for each customer.

My input dataset is like this:

customer - month - status

111-3-active

222-4-abandoned

333-5-abandoned

And i need to create a new dataset like this one:

customer - month - status

111 - 1 - active

111- 2 - active

111- 3- active

222- 1 - active

222 - 2 - active

222 - 3 - active

222 - 4 - abandoned

333 - 1 - active

333 - 2 - active

333 - 3 - active

333 - 4 - active

333 - 5 - abandoned

How can i do? Thank you in advance.

[Tiri6]

Hi,

i need to modify my input dataset in order to create a kind of log of customer's actions.

My starting dataset is:

customer - churn month - status

111         -        3             -  abandoned

222        -        5             -  active

333        -        4             -  abandoned

I need to create a dataset like this:

customer - churn month - status      

111          -           1         - active

111          -           2         - active

111          -           3         - abandoned

222        -            1         - active

222        -            2         - active

222        -            3         - active

222        -            4         - active

222        -            5         - active

333        -            1         - active

333        -            2         - active

333        -            3         - active

333        -            4         - abandoned

Could you please help me? 

Thank you in advance

[PaigeMiller]

In this problem, are the months always numbered 1 to a maximum of 5, or can there be months greater than 5? If greater than 5, how big can the month numbers go? Or is the program supposed to figure this out from the data?

Do the months always start at 1 for a customer?

[Tiri6]

I don't have a maximum number, months could ideally go from 1 to 999

[PaigeMiller]

@Tiri6 please don't post questions more than once. Your question is already answered in your other thread at https://communities.sas.com/t5/SAS-Programming/How-to-add-rows/m-p/742353#M232185

[Tiri6]

Yes i know but at first this thread was marked as spam and it wasn't published, i don't know why.

[Kurt_Bremser]

I merged everything into one thread.

[yabwon · Accepted solution]

Try this:

data have;
infile cards dlm = "-";
input customer month status $ 12.;
cards;
111-3-active
222-4-abandoned
333-5-abandoned
;
run;
proc print;
run;

data want;
  set have;
  do m = 1 to month;
    if m = month then s = status;
                 else s = 'active';
    output;
  end;
  drop month status;
  rename 
  m = month; 
  s = status;
run;
proc print;
run;

Bart

[Kurt_Bremser]

See this:

data have;
infile datalines dlm="-";
input customer $ month status :$10.;
datalines;
111-3-active
222-4-abandoned
333-5-abandoned
;

data want (rename=(_status=status));
set have;
length _status $10;
_status = "active";
do month = 1 to month - 1;
  output;
end;
_status = status;
output;
drop status;
run;

[sonofendor]

Hi! You can create multiple observations in output dataset from each input line with OUTPUT statement. More on this here: OUTPUT Statement 

In your example you can do that in a DO loop.

data work.input_set;
	input customer $ month status $15.;
	datalines;
111 3 active
222 4 abandoned
333 5 abandoned
;
run;

data work.new_set;
	set work.input_set;
	current_month = month;
	current_status = status;
	do month = 1 to current_month;
		if month < current_month then status = 'active';
			else status = current_status;
		output;
	end;
	drop current_status current_month;
run;