topic Merging two columns into one column in a way that the missing values will be filled with numbers in SAS Programming

Merging two columns into one column in a way that the missing values will be filled with numbers

farshidowrang — Tue, 10 Dec 2019 12:59:17 GMT

Dear programmers,

I have a dataset like this:

ID	COLUMN_A	COLUMN_B
1001	1	.
1002	2	.
1003	3	.
1004	4	.
1005	5	.
1006	6	6
1007	7	7
1008	.	8
1009	.	9
1010	.	10
1010	.	11
1011	.	12
1012	.	13

I want to merge COLMN_A and COLMN_B into a new column (COLUMN_C) to overwrite the missing values:

ID	COLUMN_C
1001	1
1002	2
1003	3
1004	4
1005	5
1006	6
1007	7
1008	8
1009	9
1010	10
1010	11
1011	12
1012	13

How can I do it without getting duplicates?

Best regards

Farshid

Re: Merging two columns into one column in a way that the missing values will be filled with numbers

PeterClemmensen — Tue, 10 Dec 2019 13:01:30 GMT

Like this?

What do you mean by 'without duplicates'?

data have;
input ID COLUMN_A COLUMN_B;
datalines;
1001 1 .
1002 2 .
1003 3 .
1004 4 .
1005 5 .
1006 6 6
1007 7 7
1008 . 8
1009 . 9
1010 . 10
1010 . 11
1011 . 12
1012 . 13
;

data want(keep=ID COLUMN_C);
    set have;
    COLUMN_C=coalesce(of col:);
run;

Re: Merging two columns into one column in a way that the missing values will be filled with numbers

PaigeMiller — Tue, 10 Dec 2019 13:06:27 GMT

Hello @farshidowrang

Is your example representative of all possible data in the data set, where either column_A equals column_B OR (one of column_A and column_B is missing)? Can column_A equal 8 and column_B equal 9 on the same row?

Re: Merging two columns into one column in a way that the missing values will be filled with numbers

rudfaden — Tue, 10 Dec 2019 13:14:13 GMT

you could try update.

data Master;
input ID $ Value;
datalines;
1 1
2 2
2 2
3 3
4 4
5 5
;
 
data Trans;
input ID $ Value;
datalines;
1 10
2 20
4 10
4 20
4 30
5 6
6 60
;
 
data want;
   update Master Trans updatemode=missingcheck;
   by ID;
run;

Re: Merging two columns into one column in a way that the missing values will be filled with numbers

farshidowrang — Tue, 10 Dec 2019 13:47:29 GMT

Good question

Thank you

The answer is I don't know it right now

Best regards

Farshid

Re: Merging two columns into one column in a way that the missing values will be filled with numbers

farshidowrang — Tue, 10 Dec 2019 13:50:05 GMT

nice code

Thank you very much!

I have tried it

It's a useful code

Thanx