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 |
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
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
Worked perfectly! Thanks so much!
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;
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.