topic Compare Multiple Column date values in SAS Programming

Compare Multiple Column date values

Binxie — Tue, 10 Jul 2018 17:37:47 GMT

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

Re: Compare Multiple Column date values

Reeza — Tue, 10 Jul 2018 19:02:53 GMT

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

Re: Compare Multiple Column date values

novinosrin — Tue, 10 Jul 2018 17:52:31 GMT

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;

Re: Compare Multiple Column date values

Binxie — Tue, 10 Jul 2018 18:38:04 GMT

Worked perfectly! Thanks so much!