I am trying to do format check on fields to convert into date if it is not date. It is woking fine for numeric fields but the problem is even considering date fields and converting into incorrect date.
In the below code, above two dt1 & dt2 are working fine but problem with dt3.
Data work.dataset1;
set work.dataset2;
if verify(trim(left(date1)),'0123456789')=0 Then
do;
dt1= date1- 21916;
format dt1 mmddyy10.;
end;
if verify(trim(left(date2)),'0123456789')=0 Then
do;
dt2 = date2- 21916;
format dt2 mmddyy10.;
end;
if verify(trim(left(date3)),'0123456789')=0 Then
do;
dt3= date3- 21916;
format dt3 mmddyy10.;
end;
run;
Look at this:
data have;
input date_text $;
cards;
42521
42496
05/23/2016
;
run;
data want;
set have;
format date_sas mmddyy10.;
if notdigit(substr(date_text,1,length(date_text))) > 0
then date_sas = input(date_text,mmddyy10.);
else date_sas = input(date_text,5.) - 21916;
run;
Instead of the rather complicated approach with notdigit() you might check for
if substr(date_text,3,1) = '/'
I am afraid your question is not clear. A format statement applies to the whole column, regardless of individual cells. Also, what is the input data here, it looks like your looking at a character varaible, but removing a number from it? Is this data read in from Excel by any chance? You might be able to use the anydate. format to read it in:
data work.dataset1; set work.dataset2; dt1=input(date1,anydate.); dt2=input(date2,anydate.); dt3=input(date3,anydate.); format dt1 dt2 dt3 mmddyy10.; run;
As @rivieralad has said post test data, in the form of a datastep, and what the output should look like.
Look at this:
data have;
input date_text $;
cards;
42521
42496
05/23/2016
;
run;
data want;
set have;
format date_sas mmddyy10.;
if notdigit(substr(date_text,1,length(date_text))) > 0
then date_sas = input(date_text,mmddyy10.);
else date_sas = input(date_text,5.) - 21916;
run;
Instead of the rather complicated approach with notdigit() you might check for
if substr(date_text,3,1) = '/'
Why is data in two different formats, is this what it looks like in Excel, if so fix the Excel file.
That's why Excel is considered an inadequate format for data transfer. Par with having data hammered into stone with a chisel.
@Kurt_Bremser wrote:
That's why Excel is considered an inadequate format for data transfer. Par with having data hammered into stone with a chisel.
Actually Kurt I would say it is the other way around. When you hammer into stone you know you can't move anything and therefore think about what goes where before setting chisel to stone. Excel allows stuff anywhere with no consistency and ease of moving things around means people do so without thinking about other considerations.
I agree with @Kurt_Bremser, it is one of the many, many reasons not to use Excel as a data source. However, it is also a systematic problem. Your "source data" is wrong, someone somewhere has not done their job correctly and your taking responsibility for fixing it. If what you do is wrong it will be your fault. Also, if someone else comes along, they then also have to figure out that incorrect data, maybe they have other ideas on how it should be fixed etc.
I can understand that sometimes there is push back on this, in my role we have argued for many years now with the database people that they don't want to open a database after lock to make updates. We have just put a policy in place which clearly states we as recipients of the data make no changes at all without very high level approval, they now unlock the database and update data correctly.
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.