Change Date formatt

[VipinA]

Hi,

I have a data set which has a column or variable of date in numeric eg 40753 (attribute is $10.). I want to change the format of the column to show me dates in MMYYYY formatt.

Can you help me the program to write in proc Sql.

Vipin

[PaigeMiller]

What date should 40753 appear as?

[VipinA]

052011

[PaigeMiller]

I'm sorry, I don't see how 40753 translates to 052011. Can you explain?

[VipinA]

It’s 40694

[PaigeMiller]

I still don't understand, could you please explain??

[VipinA]

I have a dataset which when loaded in SAS is giving dates in numeric numbers. Want to convert the same back to date format in SAS data set so that I can summarise records of a particular month.

I am currently unable to convert the numbers back to date formatt.

[PaigeMiller]

But you still haven't told me what the conversion is. You have not explained that. I want you to tell me what the logic is, even if you have to do it pencil and paper, explain how 40753 converts to some other appearance, in your last message you said you want to it to convert to 40694. You tell me the logic that takes 40753 and turns it into 40694, and then I can tell you what the code is.

I have a dataset which when loaded in SAS is giving dates in numeric numbers.

Also, originally you said the date value was character and now you are saying it is numeric??

[VipinA]

Hi

The number is 40694, date will be 31/5/2011

[Patrick]

---

@VipinA wrote:
Hi

The number is 40694, date will be 31/5/2011

---

Best would be if you change your import step so that the Excel date gets converted into a SAS date value (stored in a numerical variable).

...but to give you exactly what you're asking for. 

data have;
  length var $10.;
  var='40694';
run; 

data _null_;
  shiftval='01Jan1960'd -'01Jan1900'd+2;
  call symputx('shiftval', shiftval);
  stop;
run;

proc sql;
  select
    put(input(var,? best32.)-&shiftval,mmyyn6.) as var 
  from have
  ;
quit;

[unison]

Perhaps it's Excel date? Days since 1/1/1900 -- 5/31/2011 is around 40694 I imagine.

[Tom]

---

@unison wrote:

Perhaps it's Excel date? Days since 1/1/1900 -- 5/31/2011 is around 40694 I imagine.

---

It is a couple of months off from being Excel dates.  Unless the original poster is wrong about what date the number represents. To fix Excel dates add '30DEC1899'D (dates before 1960 are negative numbers).  Excel using 1900 as the based date instead of 1960 that SAS uses.  But they disagree on whether to count from zero or one and Excel mistakenly thinks that 1900 was a leap year.

2966  data x;
2967   have=40753 ;
2968   want='31MAY2011'd ;
2969   diff=want-have;
2970   put (_all_) (=comma12.);
2971   put (_all_) (=date9.);
2972
2973  run;

have=40,753 want=18,778 diff=-21,975
have=30JUL2071 want=31MAY2011 diff=01NOV1899

[Tom]

So you claim that 40,753 represents that last day in MAY of the year 2011.

Assuming that it represents the number of days since some date it is not hard to figure out what value you need add/subtract from it to get an actual date value.

2960  data x;
2961   have=40753 ;
2962   want='31MAY2011'd ;
2963   diff=want-have;
2964   put (_all_) (=comma12.);
2965  run;

have=40,753 want=18,778 diff=-21,975