SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Join Variables and Observations

Reply
Occasional Contributor
Posts: 13

Join Variables and Observations

Hi, I hope I am posting in the correct area. I would like to combine Gender1 and Gender5 with Gender5 being the winner if there is a value in both. Also for Education1 and Education5.

Is this possible in EG or EM?

I would do this in excel, but there are about 20 columns i would like to combine.

Any help would be appreciated.

Thanks!

Seth

idGender1Gender5Education1Education5
1F 1
2F 2
3F 3
4F 3
5F 1
6M 1
7F
8F 1
9F 1
10F 2
11F 1
12F 1
13FM33
14F 1
15F 1
16F 1
Super User
Posts: 5,256

Re: Join Variables and Observations

So the variables are in the same data set?

Use the coalesce function. In EG you can use the query builder and there you need to define a calculated column.

Data never sleeps
Occasional Contributor
Posts: 13

Re: Join Variables and Observations

Yes, the variables are in the same data set.

Super User
Posts: 17,784

Re: Join Variables and Observations

What do you mean by combine? Can more than one variable have a value at the same time, i.e. Gender1=F Gender5=M?

Occasional Contributor
Posts: 13

Re: Join Variables and Observations

For each ID there will only be one Gender value and one Education value like the combined columns below.

idGender CombinedGender1Gender5Education CombinedEducation1Education5
1F F11
2FF 2 2
3FF 33
4FF 33
5FF 11
6M M11
7FF
8FF 11
9FF 11
10FF 22
11FF 11
12FF 11
13MFM333
14FF 11
15FF 11
16FF 11
Super User
Posts: 17,784

Re: Join Variables and Observations

The coalesceC function is what you're looking for.

In EG can use a computed column, not sure about EM but could always write a code node.

gender_combined=coalesceC(of gender1-gender5);

Ask a Question
Discussion stats
  • 5 replies
  • 350 views
  • 0 likes
  • 3 in conversation