DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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 1Date 2Date 3
6/15/20176/14/2017 
  6/12/2017
 6/15/2017 
6/14/20176/15/20176/20/2017

 

Output should look like this

Date
6/15/2017
6/12/2017
6/15/2017
6/14/2017

 

Thank you!


Accepted Solutions
Solution
‎06-15-2017 05:33 PM
Regular Learner
Posts: 1

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

Posted in reply to chavezt87

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;

 

View solution in original post


All Replies
Super Contributor
Posts: 275

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

Posted in reply to chavezt87

date=coalesce (of date1--date3);

Frequent Contributor
Posts: 141

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

Posted in reply to chavezt87

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;

Thanks,
Suryakiran
Solution
‎06-15-2017 05:33 PM
Regular Learner
Posts: 1

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

Posted in reply to chavezt87

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;

 

New Contributor
Posts: 2

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

Thank you!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 195 views
  • 3 likes
  • 4 in conversation