BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mhoward2
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

View solution in original post

3 REPLIES 3
mkeintz
PROC Star

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.

 

 

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ballardw
Super User

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;
Tom
Super User Tom
Super User

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;

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1398 views
  • 0 likes
  • 4 in conversation