BookmarkSubscribeRSS Feed
sasuserwendy
Calcite | Level 5

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

5 REPLIES 5
art297
Opal | Level 21

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;

Linlin
Lapis Lazuli | Level 10

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             .

art297
Opal | Level 21

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;

Linlin
Lapis Lazuli | Level 10

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

sasuserwendy
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

New Learning Events in April

 

Join us for two new fee-based courses: Administrative Healthcare Data and SAS via Live Web Monday-Thursday, April 24-27 from 1:00 to 4:30 PM ET each day. And Administrative Healthcare Data and SAS: Hands-On Programming Workshop via Live Web on Friday, April 28 from 9:00 AM to 5:00 PM ET.

LEARN MORE

Discussion stats
  • 5 replies
  • 4460 views
  • 0 likes
  • 3 in conversation