topic Re: Vertical Joining in the same dataset in New SAS User

Vertical Joining in the same dataset

Bounce — Mon, 02 Dec 2019 04:38:57 GMT

Hi all,

I would like to join 3 variables in a data set which is measuring the same thing - hence I would only like a new variable with all the information from the columns and no duplicates.

The data I have:

ID 1 2 3

1 A A B

2 A B C

3 A A A

4 A B B

The data I want:

ID New_Var

1 A

1 B

2 A

2 B

2 C

3 A

4 A

4 B

If anyone can please help me, that'd be great.

Thanks in advance.

Re: Vertical Joining in the same dataset

mkeintz — Mon, 02 Dec 2019 04:45:35 GMT

Questions:

Are the data column values always in alphabetic order? (i.e. can you have B A B for a given ID?).
If they are not in alphabetic order, do you care whether the desired output honors the order the 3 values started out with? (i.e. from B A B, do you care whether the output is B A vs A B?)
Could you set up your sample data in a data step, the better to help us help you?

Re: Vertical Joining in the same dataset

Bounce — Mon, 02 Dec 2019 05:24:47 GMT

Hi mkeintz, thanks for your reply.

1. No the column values are completely random, they are actually in the format of dates in the dataset I am using.

2. No the order of the output does not matter since I can sort them later - just need them all in one column.

3. Sorry do you mean this:

DATA Example;
format ID 1.
VAR1 VAR2 VAR3 $1.;
input ID VAR1 VAR2 VAR3;
datalines;
1 A A A
2 A B C
3 A A A
4 A B B
;

run;

Re: Vertical Joining in the same dataset

Kurt_Bremser — Mon, 02 Dec 2019 08:44:00 GMT

Run a transpose:

proc transpose
  data=example
  out=want (rename=(col1=new_var))
;
by id;
var var:;
run;

Re: Vertical Joining in the same dataset

ed_sas_member — Mon, 02 Dec 2019 09:36:52 GMT

Hi @Bounce

You can try this code (proc transpose + proc sort to remove duplicate records):

proc transpose data=example out=want (rename=(col1=New_Var) drop=_name_);
	by id;
	var var:;
run;

proc sort data=want nodupkey;
	by id New_Var;
run;

Output:

Re: Vertical Joining in the same dataset

mkeintz — Mon, 02 Dec 2019 23:36:50 GMT

There is a one-step solution, which benefits from the CALL SORTC subroutine:

DATA Example;
  format ID 1.  VAR1 VAR2 VAR3 $1.;
  input ID VAR1 VAR2 VAR3;
datalines;
1 A A A
2 A B C
3 A A A
4 A B B
run;

data want (keep=id var);
  set example;
  array  _v {*} dummy var1-var3;
  call sortc(of _v{*});
  do i=2 to dim(_v);
    if missing(_v{i}) then continue;
    var=_v{i};
    if var^=_v{i-1} then output;
  end;
run;

The "if missing(_v{i}) then continue statement skips to the next iteration of the do loop, skipping the later output stateement.
I put a dummy variable at the lower bound of the array, to smiplify the "if var^=_v{i-1}" test.