Combining two variables into one without losing any observations

[kmardinian]

Hi, I have a complicated dataset that contains two different variables that represent the same thing. So I would like to combine both variables together so that the final single variable is all the observations from both variables. I don't want to lose any observations. But for some reason I am having a hard time figuring out how to do this in SAS. Any help would be much appreciated. Thank you!

[novinosrin]

Could you post a sample of what you HAVE and what you WANT to help avoid assumptions

[kmardinian]

Yes, here is a simplified example of my dataset

ID       CR      CR1      

 x           1           2

 x           4           5

 y           1           3

 z           1           4

and I would like the final variable to be

ID       CR2

x          1

x          4

x          2

x          5

y          1

y          3

z         1

z         4

Thank you!

[Reeza]

Did you create the data set? Or is that the starting form?

---

@kmardinian wrote:

Yes, here is a simplified example of my dataset

ID       CR      CR1      

 x           1           2

 x           4           5

 y           1           3

 z           1           4

and I would like the final variable to be

ID       CR2

x          1

x          4

x          2

x          5

y          1

y          3

z         1

z         4

Thank you!

---

[kmardinian]

I created this dataset, it is multiple datasets that have been merged together into one dataset

[Reeza]

---

@kmardinian wrote:

I created this dataset, it is multiple datasets that have been merged together into one dataset

---

You shouldn't have merged, you should append instead. Go back a step and fix it there instead.

You can use either PROC APPEND or a data step.

[kmardinian]

Sorry, I misspoke, I did not merge the datasets, but set all the datasets into one, because I do not want to lose any observations

data t1;

set d1-d10;

[Astounding · Accepted solution]

Is this too simple and obvious as a solution?

data want;

set have;

cr2=cr;

output;

cr2=cr1;

output;

drop cr cr1;

run;

[kmardinian]

Thank you! This was what I was looking for. I apologize for not having been clear in my previous posts. I'll make sure to include a data step the next time I have a question. Thank you all for your help!

[ballardw]

---

@kmardinian wrote:

Yes, here is a simplified example of my dataset

ID       CR      CR1      

 x           1           2

 x           4           5

 y           1           3

 z           1           4

and I would like the final variable to be

ID       CR2

x          1

x          4

x          2

x          5

y          1

y          3

z         1

z         4

Thank you!

---

Is there a serious reason why this output would not be acceptable?

ID       CR2
x          1
x          2
x          4
x          5
y          1
y          3
z          1
z          4

And if other variables are involved that do not get "moved" then you need to show what a few of them look like before and after

[Reeza]

How do you want to combine them? Are all combined? In the same way? No cases are different? If not, why not just drop one? We need significantly more details to even start with this question.
Though a random guesses would be COALESCE or CATX.

[novinosrin]

data have;
input iD  $     CR      CR1      ;
cards;
 x           1           2
 x           4           5
 y           1           3
 z           1           4
 ;

proc transpose data=have out=want(keep= id col1) ;
by id cr cr1;
var cr cr1;
run;

[kmardinian]

I have many other variables in the dataset too, would proc transpose mess with them? Thank you!

[novinosrin]

Unfortunately yes, can you present a better and comprehensive  sample if you want me to work with. I must admit I am not sound at advisory level to foresee and recommend, however if somebody can provide you that, hang in there.

Nevertheless, should you provide best representative samples, i enjoy coding 🙂

[mkeintz]

I think you can see why it is recommended that a topic originator show a data step with data showing the starting organization (for instance are you showing us the actual layout of, say, data set D1 and D2?  I can't tell yet.   I had assume that @ballardw's suggestion addressed the information as you provided it, but we won't know until you tell us.