topic Re: How to add rows in SAS Programming

How to add rows

Tiri6 — Wed, 19 May 2021 08:55:08 GMT

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.

How to add rows in SAS

Tiri6 — Wed, 19 May 2021 07:46:20 GMT

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

Re: How to add rows

yabwon — Wed, 19 May 2021 09:29:28 GMT

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

Re: How to add rows

Kurt_Bremser — Wed, 19 May 2021 09:29:44 GMT

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;

Re: How to add rows

sonofendor — Wed, 19 May 2021 09:33:41 GMT

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;

Re: How to add rows in SAS

PaigeMiller — Wed, 19 May 2021 12:02:37 GMT

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?

Re: How to add rows in SAS

Tiri6 — Wed, 19 May 2021 12:01:56 GMT

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

Re: How to add rows in SAS

PaigeMiller — Wed, 19 May 2021 12:27:35 GMT

@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

Re: How to add rows in SAS

Tiri6 — Wed, 19 May 2021 12:29:05 GMT

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

Re: How to add rows in SAS

Kurt_Bremser — Wed, 19 May 2021 12:37:10 GMT

I merged everything into one thread.