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
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;
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;
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?
Good question
Thank you
The answer is I don't know it right now
Best regards
Farshid
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;
nice code
Thank you very much!
I have tried it
It's a useful code
Thanx
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.