Restrict date to mm/dd/yyyy format from input csv file and flag other rows with diff format

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Restrict date to mm/dd/yyyy format from input csv file and flag other rows with diff format

Hello,

                    i have input file which i need to read and check the format of column in the file should be MM/DD/YYYY. If it is any other format like MM-DD-YYYY i need to flag it to remove it. What will be best possible solution?. i am using informat mmddyy10. but it reads both format which is expected.


Accepted Solutions
Solution
‎04-11-2017 02:30 PM
Super User
Posts: 11,105

Re: Restrict date to mm/dd/yyyy format from input csv file and flag other rows with diff format

If you don't like the - then you may want to read the values as character first then search for  for any other character than / in the character value. If you data is consistetn then 

flag =  (index(characterdate,'/')=0 );

would set a flag of 1 for no / in the value.

then likely follow with

If flag=0 then datevalue = input(characterdate,mmddyy10.);

 

View solution in original post


All Replies
Solution
‎04-11-2017 02:30 PM
Super User
Posts: 11,105

Re: Restrict date to mm/dd/yyyy format from input csv file and flag other rows with diff format

If you don't like the - then you may want to read the values as character first then search for  for any other character than / in the character value. If you data is consistetn then 

flag =  (index(characterdate,'/')=0 );

would set a flag of 1 for no / in the value.

then likely follow with

If flag=0 then datevalue = input(characterdate,mmddyy10.);

 

Trusted Advisor
Posts: 1,459

Re: Restrict date to mm/dd/yyyy format from input csv file and flag other rows with diff format

data want;

       infile ...;

       input 

           ...

           datex $10.

          ...

      ;

      daten = input(datex, mmddyy10.);

      IF daten = . or

          substr(datex,3,1) ne '/' or

          substr(datex,6,1) ne '/'

          then flag = 1;

      else flag = 0;

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 225 views
  • 0 likes
  • 3 in conversation