topic Combining two variables into one without losing any observations in SAS Programming

Combining two variables into one without losing any observations

kmardinian — Fri, 28 Sep 2018 20:32:40 GMT

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!

Re: Combining two variables into one without losing any observations

novinosrin — Fri, 28 Sep 2018 20:43:43 GMT

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

Re: Combining two variables into one without losing any observations

Reeza — Fri, 28 Sep 2018 20:46:39 GMT

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.

Re: Combining two variables into one without losing any observations

kmardinian — Fri, 28 Sep 2018 20:49:04 GMT

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!

Re: Combining two variables into one without losing any observations

Reeza — Fri, 28 Sep 2018 20:50:37 GMT

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!

Re: Combining two variables into one without losing any observations

kmardinian — Fri, 28 Sep 2018 20:51:42 GMT

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

Re: Combining two variables into one without losing any observations

novinosrin — Fri, 28 Sep 2018 20:56:41 GMT

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;

Re: Combining two variables into one without losing any observations

Reeza — Fri, 28 Sep 2018 20:56:52 GMT

@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.

Re: Combining two variables into one without losing any observations

kmardinian — Fri, 28 Sep 2018 20:58:39 GMT

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;

Re: Combining two variables into one without losing any observations

kmardinian — Fri, 28 Sep 2018 21:05:33 GMT

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

Re: Combining two variables into one without losing any observations

novinosrin — Fri, 28 Sep 2018 21:12:28 GMT

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 🙂

Re: Combining two variables into one without losing any observations

Astounding — Fri, 28 Sep 2018 21:16:41 GMT

Is this too simple and obvious as a solution?

data want;

set have;

cr2=cr;

output;

cr2=cr1;

output;

drop cr cr1;

run;

Re: Combining two variables into one without losing any observations

ballardw — Fri, 28 Sep 2018 21:39:10 GMT

@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

Re: Combining two variables into one without losing any observations

mkeintz — Sat, 29 Sep 2018 00:55:15 GMT

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.

Re: Combining two variables into one without losing any observations

kmardinian — Sun, 30 Sep 2018 23:07:33 GMT

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!