topic Best way to handle multple date formats in one variable [data cleaning] in ODS and Base Reporting

Best way to handle multple date formats in one variable [data cleaning]

kkwan — Fri, 29 May 2020 20:39:08 GMT

I have a variable "date"

In most instances the date is reported as "YYYYMMDD" but in some instances the day is missing, so I only have "YYYYMM"

I ultimately want a date variable which looks like "MM/DD/YYYY"

I know that if everything in the original date variable was "YYYYMMDD" then I could do:

new_date = input(put(date,8.),yymmdd8.);
format new_date mmddyy10.;

But since some "date" values are only 6 characters long it's throwing up error signs. I figure that if the date is missing I'll set the day to the first of the month.

How could I set dates which only have a length of 6 to have a day value of "01" added to the end? I should be able to convert the new "YYYYMMDD" variable to "MM/DD/YYYY" myself afterwards.

Thanks in advance

Re: Best way to handle multple date formats in one variable [data cleaning]

biopharma — Fri, 29 May 2020 21:04:24 GMT

One way to trick this is to define a character variable that does not hold any more than 10 spaces to fully accommodate YYYY-MM-DD. Then you can append "-01-01". Only partial dates will be able to add the extra values.

data have ;
  length havedateC $15 ;
  havedateC = "2020-05-29" ;
  output ;
  havedateC = "2020-05" ;
  output ;
  havedateC = "2020" ;
  output ;
run ;

data want ;
   set have ;
   length fulldateC $10 ;
   fulldateC = strip(havedateC)||"-01-01" ;
   fulldateN = input(fulldateC,yymmdd10.) ;
   format fulldateN yymmdd10. ;
run ;

proc print ;
run ;


Obs	havedateC	fulldateC	fulldateN
1	2020-05-29	2020-05-29	2020-05-29
2	2020-05	2020-05-01	2020-05-01
3	2020	2020-01-01	2020-01-01

Re: Best way to handle multple date formats in one variable [data cleaning]

ballardw — Fri, 29 May 2020 21:09:14 GMT

Depending on your current variable type:

data junk;
  /* if your date was character*/
  input date $;
  if length(date)=8 then datevar=input(date,yymmdd10.);
  else if length(date)=6 then datevar=input(date,yymmn6.);
  format datevar mmddyy10.;

datalines;
20200305
202004
;

data junk2;
  /* if your date was numeric*/
  input date ;
  if length(put(date,best8. -L))=8 then datevar=input(put(date,best8. -L),yymmdd10.);
  else if length(put(date,best8. -L))=6 then datevar=input(put(date,best8. -L),yymmn6.);
  format datevar mmddyy10.;

datalines;
20200305
202004
;

The YYMMN informat assumes the value is only year and month and treats it as the first day of the month.