topic A strange problem with date format. Never seen this before. Need help! in SAS Health and Life Sciences

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

sasuserwendy — Thu, 02 Feb 2012 03:59:39 GMT

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

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

art297 — Thu, 02 Feb 2012 04:20:26 GMT

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;

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

Linlin — Thu, 02 Feb 2012 13:40:18 GMT

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

;

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 .

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

art297 — Thu, 02 Feb 2012 13:46:34 GMT

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;

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

Linlin — Thu, 02 Feb 2012 13:55:46 GMT

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

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

sasuserwendy — Thu, 02 Feb 2012 22:12:41 GMT

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