Duplicate row of data when condition met and change the specific value

[apeeape]

I am wondering if there is a way to duplicate the row of data when condition met, and change the value of a specific variable.

For example the original data I have is:
Name Sex Income
Amy F 12635
Bob M 13729
Kim F 16438,18973
Zoe F 15307

I want to duplicate the row of data when there is a comma “,” in the income variable, expect the income variable since I want to show that there are 2 different incomes for that person.

This is the expected output:
Name Sex Income
Amy F 12635
Bob M 13729
Kim F 16438
Kim F 18973
Zoe F 15307

I tried to duplicate the data when there is a comma in the income variable but I don’t know how to change the income value.

data updated_income;
Set income;
If findw(income,”,”) then output;
Run;

[r_behata · Accepted solution]

data have;
input Name $ Sex $ Income :$20.;
cards;
Amy F 12635
Bob M 13729
Kim F 16438,18973
Zoe F 15307
run;


data want;
	set have;
	
	do _n_=1 to countw(Income,',');
			new_Income=scan(Income,_n_,',');
		output;
	end;
run;

[ballardw]

---

@apeeape wrote:
I am wondering if there is a way to duplicate the row of data when condition met, and change the value of a specific variable.

For example the original data I have is:
Name Sex Income
Amy F 12635
Bob M 13729
Kim F 16438,18973
Zoe F 15307

I want to duplicate the row of data when there is a comma “,” in the income variable, expect the income variable since I want to show that there are 2 different incomes for that person.

This is the expected output:
Name Sex Income
Amy F 12635
Bob M 13729
Kim F 16438
Kim F 18973
Zoe F 15307

I tried to duplicate the data when there is a comma in the income variable but I don’t know how to change the income value.

data updated_income;
Set income;
If findw(income,”,”) then output;
Run;

---

One way. The first data step creates a data set similar to what you show. The second extracts the income values into a numeric variable (much more typical for analysis of income).

data have;
  input Name $ Sex $ Income :$12. ;
datalines;
Amy F 12635
Bob M 13729
Kim F 16438,18973
Zoe F 15307
;

data want;
   set have;
   if index(income,',') then do i=1 to countw(income,',');
     incomenum = input(scan(income,i,','),f12.);
     output;
   end;
   else do;
      incomenum= input(income,f12.);
      output;
   end;
   /*drop i income;*/
run;

The explicit OUTPUT statement executes when you want to write to the output data set.

The COUNTW function as shown uses the comma to delimit the values and returns how many values you have. Then we extract each one with the SCAN function and use input to create the numeric variable. I have commented out a drop statement to remove variables that you may not need.

If your really must have the output variable with the income information named Income then

data want (rename=(incomenum = income));
   set have;
   if index(income,',') then do i=1 to countw(income,',');
     incomenum = input(scan(income,i,','),f12.);
     output;
   end;
   else do;
      incomenum= input(income,f12.);
      output;
   end;
   drop i income;
run;

You needed at least one more variable to hold the value of Income while creating a new variable with each piece. There are other ways to this. You could rename the Income variable on the Set statement similar to as done above on the DATA statement.