A strange problem with date format. Never seen this before. Need help!

Reply
New Contributor
Posts: 2

A strange problem with date format. Never seen this before. Need help!

Hello,

I've read 'date' related posts but they couldn't help my problem. Here is what bothering me:

The sas file came in with a date variable, rev_dt. For example, '20040525', a length of 8, format Best12., informat 12. The raw data also filled in '0' for the observations without a date.

However, seems I cannot reformat/or delete the original format of the variable. I tried multiple ways to work with the problem:

1. I tried newname=put(rev_dt,  yymmdd10.) at data step.  The new variable only showed '*********' for all observations like '20040525', unreadable. The '0' were successfully converted to '1960-01-01'.

2. I tried to informat or format at datastep:

data try;

format rev_dt yymmdd10.;

set try;

run;

Again, '0's were converted successful. But observations like '20040525' stayed exactly the same. I always got the error message saying "There was a problem with the format so Best. was used", or "Mathematical Operations could not be performed" when I substract yr=year(rev_dt).

3. If I do:

data try;

format rev_dt date.;

set try;

run;

Then '20040525' and all other similar observations became '2.004E7'. '0's became '01Jan60'.


I need the convertion so that I can append this data file with the second one (with dates format like 2004-05-25) and delete the duplicates. Right now after the append, duplicated dates would not go because they are listed differently.

I have tried to conver the second dataset into '20040525' format, but the duplicated dates would not be deleted either even when they looked exactly the same.

Anyone can give me some help? Greatly appreciate it!

Thank you!

Wendy

Esteemed Advisor
Posts: 6,883

A strange problem with date format. Never seen this before. Need help!

I don't think you need to create a new variable.  Wouldn't something like the following suffice?:

data have;

  informat rev_dt 12.;

  format rev_dt best12.;

  length rev_dt 8;

  input rev_dt;

  cards;

20040525

20050221

;

data want;

  set have;

  format rev_dt date9.;

  rev_dt=input(put(rev_dt,z8.),yymmdd8.);

run;

Super Contributor
Posts: 1,636

Re: A strange problem with date format. Never seen this before. Need help!

I added a observation with rev_dt=0 to Art's code:

data have;

  informat rev_dt 12.;

  format rev_dt best12.;

  length rev_dt 8;

  input rev_dt;

  cards;

20040525

20050221

0

;

data want;

  set have;

  format rev_dt date9.;

  if rev_dt=0 then rev_dt=.;

  else

  rev_dt=input(put(rev_dt,z8.),yymmdd8.);

run;

proc print;run;

                                        Obs       rev_dt

                                          1     25MAY2004

                                          2     21FEB2005

                                          3             .

Esteemed Advisor
Posts: 6,883

A strange problem with date format. Never seen this before. Need help!

Linlin,

Zero dates could also be accomodated by just adding ??  E.g.:

data want;

  set have;

  format rev_dt date9.;

  rev_dt=input(put(rev_dt,z8.), ?? yymmdd8.);

run;

Super Contributor
Posts: 1,636

A strange problem with date format. Never seen this before. Need help!

Thank you Art! it is good to know.  - Linlin

New Contributor
Posts: 2

A strange problem with date format. Never seen this before. Need help!

Love you guys/gals!! What you recommended really solved my problem beautifully! Thank you soooooo much!

Post a Question
Discussion Stats
  • 5 replies
  • 3300 views
  • 0 likes
  • 3 in conversation