topic Re: How do I combine multiple columns with missing data and prioritizing certain columns using Proc in SAS Programming

How do I combine multiple columns with missing data and prioritizing certain columns using Proc SQL

chavezt87 — Thu, 15 Jun 2017 17:57:58 GMT

Hello,

I'm have a dataset with 3 columns of dates. What I would like to do is create one single column which prioritizes putting Date 1 in it, but if Date 1 is missing it places Date 2 into the new column and finally if both Date 1 and Date 2 are missing it places Date 3 into the new column.

Date 1	Date 2	Date 3
6/15/2017	6/14/2017
		6/12/2017
	6/15/2017
6/14/2017	6/15/2017	6/20/2017

Output should look like this

Date

6/15/2017

6/12/2017

6/15/2017

6/14/2017

Thank you!

Re: How do I combine multiple columns with missing data and prioritizing certain columns using Proc

slchen — Thu, 15 Jun 2017 18:08:03 GMT

date=coalesce (of date1--date3);

Re: How do I combine multiple columns with missing data and prioritizing certain columns using Proc

SuryaKiran — Thu, 15 Jun 2017 18:14:33 GMT

You can use IF THEN condition.

IF NOT MISSING(DATE1) THEN DATE=DATE1;
ELSE IF MISSING(DATE1) AND NOT MISSING(DATE2) THEN DATE=DATE2;
ELSE DATE=DATE3;

Re: How do I combine multiple columns with missing data and prioritizing certain columns using Proc

vsing154 — Thu, 15 Jun 2017 18:41:15 GMT

proc sql;

select case

when date1 ne . then date1

when date1=. and date2 ne . then date 2

when date1=. and date2=. then date3

end as date from table;

quit;

Re: How do I combine multiple columns with missing data and prioritizing certain columns using Proc

chavezt87 — Thu, 15 Jun 2017 21:33:39 GMT

Thank you!