Hi! I'm a new-ish SAS user, working with field survey data collected by an NGO. I'm using the most recent version of SAS University. I have 3 different SAS date fields, date_pre2, start_pre3, and end_pre3. The dates for these 3 variables are almost always the same, with a few exceptions. I want to use date_pre2, but in a few cases because of likely entry errors, I want to use the year, month, day, or whole date from start_pre3.
Here's an example of my dataset (there are actually ~500 cases). For id 372, for example, I want to replace the year of date_pre2 (2023) with the year of start_pre3 (2017).
id | city | community | hhnum2 | date_pre2 | start_pre3 | end_pre3 |
110 | ABCD | B-ville | 1 | 2018-01-12 | 2017-03-12 | 2017-03-12 |
183 | ABCD | B-ville | 2 | 2017-10-19 | 2016-10-19 | 2016-10-19 |
368 | ABCD | Jtown | 3 | 2018-08-18 | 2017-08-18 | 2017-08-21 |
372 | WXYZ | Jtown | 4 | 2023-08-01 | 2017-08-18 | 2017-08-19 |
379 | WXYZ | K-town | 5 | 2018-08-22 | 2017-08-22 | 2017-08-22 |
Here's the code I used to make the changes. I didn't create a new variable in this case since there are just a handful of changes to make.
data pre_amd2; set pre_agematchdate;
if id IN (183, 368, 379) then year(date_pre2) = year(start_pre3);
if id IN (119, 191, 194, 202, 224, 412) then month(date_pre2) = month(start_pre3);
if id = 369 then day(date_pre2) = day(start_pre3);
if id IN (110, 372) then date_pre2 = start_pre3;
run;
When I run this, I get this error.
This logic is incorrect:
if id IN (119, 191, 194, 202, 224, 412) then month(date_pre2) = month(start_pre3);
What that is doing is checking if month(date_pre2) is equal the month(start_pre3) which is the month of both dates, it doesn't assign any value.
You can use MDY() to create the date, getting the components from different sections.
if id IN (119, 191, 194, 202, 224, 412) then date_pre2 = mdy(month(start_pre3), day(start_pre2), year(start_pre2));
You could generalize it and replace specific sections, but this is perfectly valid and works well.
@panchang wrote:
Hi! I'm a new-ish SAS user, working with field survey data collected by an NGO. I'm using the most recent version of SAS University. I have 3 different SAS date fields, date_pre2, start_pre3, and end_pre3. The dates for these 3 variables are almost always the same, with a few exceptions. I want to use date_pre2, but in a few cases because of likely entry errors, I want to use the year, month, day, or whole date from start_pre3.
Here's an example of my dataset (there are actually ~500 cases). For id 372, for example, I want to replace the year of date_pre2 (2023) with the year of start_pre3 (2017).
id
city
community
hhnum2
date_pre2
start_pre3
end_pre3
110
ABCD
B-ville
1
2018-01-12
2017-03-12
2017-03-12
183
ABCD
B-ville
2
2017-10-19
2016-10-19
2016-10-19
368
ABCD
Jtown
3
2018-08-18
2017-08-18
2017-08-21
372
WXYZ
Jtown
4
2023-08-01
2017-08-18
2017-08-19
379
WXYZ
K-town
5
2018-08-22
2017-08-22
2017-08-22
Here's the code I used to make the changes. I didn't create a new variable in this case since there are just a handful of changes to make.
data pre_amd2; set pre_agematchdate; if id IN (183, 368, 379) then year(date_pre2) = year(start_pre3); if id IN (119, 191, 194, 202, 224, 412) then month(date_pre2) = month(start_pre3); if id = 369 then day(date_pre2) = day(start_pre3); if id IN (110, 372) then date_pre2 = start_pre3; run;
When I run this, I get this error.
ERROR: Undeclared array referenced: year.ERROR: Variable year has not been declared as an array.(Same error message for month and day)Can anyone help me figure out the right way to do this? Should I define a new variable instead? I'm not really familiar with sql or macros. Thanks! - Sarita
This logic is incorrect:
if id IN (119, 191, 194, 202, 224, 412) then month(date_pre2) = month(start_pre3);
What that is doing is checking if month(date_pre2) is equal the month(start_pre3) which is the month of both dates, it doesn't assign any value.
You can use MDY() to create the date, getting the components from different sections.
if id IN (119, 191, 194, 202, 224, 412) then date_pre2 = mdy(month(start_pre3), day(start_pre2), year(start_pre2));
You could generalize it and replace specific sections, but this is perfectly valid and works well.
@panchang wrote:
Hi! I'm a new-ish SAS user, working with field survey data collected by an NGO. I'm using the most recent version of SAS University. I have 3 different SAS date fields, date_pre2, start_pre3, and end_pre3. The dates for these 3 variables are almost always the same, with a few exceptions. I want to use date_pre2, but in a few cases because of likely entry errors, I want to use the year, month, day, or whole date from start_pre3.
Here's an example of my dataset (there are actually ~500 cases). For id 372, for example, I want to replace the year of date_pre2 (2023) with the year of start_pre3 (2017).
id
city
community
hhnum2
date_pre2
start_pre3
end_pre3
110
ABCD
B-ville
1
2018-01-12
2017-03-12
2017-03-12
183
ABCD
B-ville
2
2017-10-19
2016-10-19
2016-10-19
368
ABCD
Jtown
3
2018-08-18
2017-08-18
2017-08-21
372
WXYZ
Jtown
4
2023-08-01
2017-08-18
2017-08-19
379
WXYZ
K-town
5
2018-08-22
2017-08-22
2017-08-22
Here's the code I used to make the changes. I didn't create a new variable in this case since there are just a handful of changes to make.
data pre_amd2; set pre_agematchdate; if id IN (183, 368, 379) then year(date_pre2) = year(start_pre3); if id IN (119, 191, 194, 202, 224, 412) then month(date_pre2) = month(start_pre3); if id = 369 then day(date_pre2) = day(start_pre3); if id IN (110, 372) then date_pre2 = start_pre3; run;
When I run this, I get this error.
ERROR: Undeclared array referenced: year.ERROR: Variable year has not been declared as an array.(Same error message for month and day)Can anyone help me figure out the right way to do this? Should I define a new variable instead? I'm not really familiar with sql or macros. Thanks! - Sarita
Hi Reeza, I see what you're saying now. I tailored your code to the other changes on the date I needed to make, checked with proc print, and it all seems to work. Thanks a lot!
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.