topic Duplicate Rows But Change One Variable in SAS Programming

Duplicate Rows But Change One Variable

mhoward2 — Wed, 23 Aug 2023 13:24:15 GMT

I have a table that looks like the following:

ID1	ID2	Type	Value1
A	C	Red	3
A	C	Blue	2
A	D	Green	2
A	D	Purple	1
B	C	Orange	3
B	C	Yellow	2
B	D	Brown	1

And want to duplicate rows if ID2 = 'D', and then change ID2 to 'C'.

Meaning I want a new table that looks like:

ID1	ID2	Type	Value1
A	C	Red	3
A	C	Blue	2
A	D	Green	2
A	D	Purple	1
B	C	Orange	3
B	C	Yellow	2
B	D	Brown	1
A	C	Green	2
A	C	Purple	1
B	C	Brown	1

You can see the last three rows in that table are the D rows in the first table, relabeled as C, but also kept the original D rows.

Any ideas? Thanks in advance!

Re: Duplicate Rows But Change One Variable

mkeintz — Wed, 23 Aug 2023 13:35:26 GMT

What have you tried so far? Presumably you can start out with code using IF tests and OUTPUT statements. Instead of just providing the code to you for this problem, let us help you own the problem instead of the other way.

Why not start out breaking the problem into two issues: (1) making the modified duplicates, and (2) putting those duplicates in order at the end of the original - i.e. appending the duplicates.

Re: Duplicate Rows But Change One Variable

ballardw — Wed, 23 Aug 2023 14:29:55 GMT

Is that "new table" the required ORDER of the values? It is a bit easier to not have the output order as shown.

This splits the data into two sets and recombines to main that shown order. If the order of the observations isn't important than the second data step can create the Want set by Output to one data set instead of two.

Please not use of a data step to provide data in a form that can be used with code:

data have;
   input ID1 $	ID2 $	Type $	Value1;
datalines;
A 	C 	Red 	3
A 	C 	Blue 	2
A 	D 	Green 	2
A 	D 	Purple 	1
B 	C 	Orange 	3
B 	C 	Yellow 	2
B 	D 	Brown 	1
;

data t1 t2;
   set have;
   output t1;
   if id2='D' then do;
      id2='C';
      output t2;
   end;
run;

data want;
   set t1 t2;
run;

Re: Duplicate Rows But Change One Variable

Tom — Wed, 23 Aug 2023 14:39:53 GMT

And want to duplicate rows if ID2 = 'D', and then change ID2 to 'C'.

An easy way to duplicate rows it just to write them twice.

data double;
  set have;
  output;
  output;
run;

For your change you need to modify the value before writing them out. So not after the duplication, but before.

data want;
  set have;
  output;
  if id2='D' then do;
     id2='C';
    output;
  end;
run;

Another way to think of it is you want to make NEW observations based on the observations that have ID2='D';

data new;
  set have;
  where id2='D';
  id2='C';
run;

Then combine them with the original data.

data want;
  set have new;
run;