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!
Is this too simple and obvious as a solution?
data want;
set have;
cr2=cr;
output;
cr2=cr1;
output;
drop cr cr1;
run;
Could you post a sample of what you HAVE and what you WANT to help avoid assumptions
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!
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!
I created this dataset, it is multiple datasets that have been merged together into one dataset
@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.
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;
Is this too simple and obvious as a solution?
data want;
set have;
cr2=cr;
output;
cr2=cr1;
output;
drop cr cr1;
run;
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!
@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
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;
I have many other variables in the dataset too, would proc transpose mess with them? Thank you!
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 🙂
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.
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!
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.