DATA Step, Macro, Functions and more

Compare Multiple Column date values

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Compare Multiple Column date values

Hello SAS Community,
I'm a relatively newbie and I'm hoping you can help me.  I have a dataset that has multiple columns that are in a date format.  I need to confirm if all dates in the columns are the same, or if one or more of them are different. If any column has a different value I wish to set a flag that indicates this. 

 

Is there an easy way to do this?  See my sample data and expected output below.  Thanks in advance!

 

 

Data example:

 

ACCOUNTCOL1COL2COL3COL4COL5COL6COL7COL8COL9
19-Jun-189-Oct-189-Jun-18      
29-Jun-189-Jun-18       
39-Jun-189-Jun-189-Jun-189-Apr-189-Jun-189-Jun-189-Jun-189-Nov-189-Jun-18
49-Aug-189-Jun-189-Jun-189-Jun-189-Jun-18    
59-Jun-189-Jun-18       

 

I would like to output as follows:

 

ACCOUNTCOL1COL2COL3COL4COL5COL6COL7COL8COL9FLAG
19-Jun-189-Oct-189-Jun-18      different
29-Jun-189-Jun-18       same
39-Jun-189-Jun-189-Jun-189-Apr-189-Jun-189-Jun-189-Jun-189-Nov-189-Jun-18different
49-Aug-189-Jun-189-Jun-189-Jun-189-Jun-18    different
59-Jun-189-Jun-18       same

Accepted Solutions
Solution
a week ago
Super User
Posts: 23,721

Re: Compare Multiple Column date values

[ Edited ]

If the maximum is the same as the minimum they're all the same, if not they're different.

if max(of col1-col9)=min(of col1-col9) then flag='Same'; else flag='Different';

You may have to list out your variables if you don't have a naming convention.

 


@Binxie wrote:

Hello SAS Community,
I'm a relatively newbie and I'm hoping you can help me.  I have a dataset that has multiple columns that are in a date format.  I need to confirm if all dates in the columns are the same, or if one or more of them are different. If any column has a different value I wish to set a flag that indicates this. 

 

Is there an easy way to do this?  See my sample data and expected output below.  Thanks in advance!

 

 

Data example:

 

ACCOUNT COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9
1 9-Jun-18 9-Oct-18 9-Jun-18            
2 9-Jun-18 9-Jun-18              
3 9-Jun-18 9-Jun-18 9-Jun-18 9-Apr-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Nov-18 9-Jun-18
4 9-Aug-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Jun-18        
5 9-Jun-18 9-Jun-18              

 

I would like to output as follows:

 

ACCOUNT COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 FLAG
1 9-Jun-18 9-Oct-18 9-Jun-18             different
2 9-Jun-18 9-Jun-18               same
3 9-Jun-18 9-Jun-18 9-Jun-18 9-Apr-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Nov-18 9-Jun-18 different
4 9-Aug-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Jun-18         different
5 9-Jun-18 9-Jun-18               same

 

View solution in original post


All Replies
Solution
a week ago
Super User
Posts: 23,721

Re: Compare Multiple Column date values

[ Edited ]

If the maximum is the same as the minimum they're all the same, if not they're different.

if max(of col1-col9)=min(of col1-col9) then flag='Same'; else flag='Different';

You may have to list out your variables if you don't have a naming convention.

 


@Binxie wrote:

Hello SAS Community,
I'm a relatively newbie and I'm hoping you can help me.  I have a dataset that has multiple columns that are in a date format.  I need to confirm if all dates in the columns are the same, or if one or more of them are different. If any column has a different value I wish to set a flag that indicates this. 

 

Is there an easy way to do this?  See my sample data and expected output below.  Thanks in advance!

 

 

Data example:

 

ACCOUNT COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9
1 9-Jun-18 9-Oct-18 9-Jun-18            
2 9-Jun-18 9-Jun-18              
3 9-Jun-18 9-Jun-18 9-Jun-18 9-Apr-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Nov-18 9-Jun-18
4 9-Aug-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Jun-18        
5 9-Jun-18 9-Jun-18              

 

I would like to output as follows:

 

ACCOUNT COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 FLAG
1 9-Jun-18 9-Oct-18 9-Jun-18             different
2 9-Jun-18 9-Jun-18               same
3 9-Jun-18 9-Jun-18 9-Jun-18 9-Apr-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Nov-18 9-Jun-18 different
4 9-Aug-18 9-Jun-18 9-Jun-18 9-Jun-18 9-Jun-18         different
5 9-Jun-18 9-Jun-18               same

 

New Contributor
Posts: 2

Re: Compare Multiple Column date values

Worked perfectly!  Thanks so much!

PROC Star
Posts: 1,803

Re: Compare Multiple Column date values

data have;
infile cards truncover;
input ACCOUNT	(COL1	COL2	COL3	COL4	COL5	COL6	COL7	COL8	COL9) (:date9.);
format col: date9.;
cards;
1	9-Jun-18	9-Oct-18	9-Jun-18	 	 	 	 	 	 
2	9-Jun-18	9-Jun-18	 	 	 	 	 	 	 
3	9-Jun-18	9-Jun-18	9-Jun-18	9-Apr-18	9-Jun-18	9-Jun-18	9-Jun-18	9-Nov-18	9-Jun-18
4	9-Aug-18	9-Jun-18	9-Jun-18	9-Jun-18	9-Jun-18	 	 	 	 
5	9-Jun-18	9-Jun-18	 	 	 	 	 	 
;
data want;
set have;
FLAG=ifc(range(of col:)=0,'SAME','DIFFERENT');
run;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 72 views
  • 3 likes
  • 3 in conversation