Renaming and formatting date variables

[renjithr]

Hi All,

Input file: is an excel file read into SAS. Mother_dob is a numeric variable with the value 20130123 in the file Test.

Wanted output file: I want mother_dob with the format yymmddn8

Result I got with the following code: the output file is still showing mother_dob with the yymmdd8. format.

I noticed that in my following code after I do the formating of the date variable the values are still showing as in SAS date values. Could you please help me understand why the format yymmddn8 is not getting assigned in the below code.

DATA final;

SET test(keep=mother_dob obs=1);

         MOTHER_DOB1     = input(put(left(MOTHER_DOB),8.),YYMMDD8.);

      drop MOTHER_DOB ;

      rename MOTHER_DOB1     = MOTHER_DOB;

      format MOTHER_DOB yymmddn8.;

RUN;

I was able to assign mother_dob with yymmddn8. format with the following code but still did not understand why the above code did not work.

Modified code:

DATA final;

SET test(keep=mother_dob obs=1);

         MOTHER_DOB1     = input(put(left(MOTHER_DOB),8.),YYMMDD8.);

       format MOTHER_DOB1 yymmddn8.;

      drop MOTHER_DOB ;

      rename MOTHER_DOB1     = MOTHER_DOB;

  RUN;

Please share your thoughts. Thank you all in advance!  

[ballardw]

What results do you get for your new dob variable? Show the actual input and resulting values for some records. Otherwise it is a bit hard to even know where to start.

It may be revealing to show what the value of the original put(mother_dob, 8.) is.

[renjithr]

Value of dob in the input file:  20130123  , type numeric

With the first set of code I got the value for dob as : 19381, type numeric

With second set of code I got the value for dob as :  20130123, format yymmddn8.

Hope this helps.

[Tom]

The first one doesn't work because you never attached a format to your new variable.  Instead you attached it to the one that you dropped.  If your variable is numeric then you do NOT want the LEFT() function as that will convert it to a string which will then probably cause the PUT() function to use $8. format instead of the 8. format that it would have used if the value passed it was a number.

data final;

  set test(keep=mother_dob obs=1);

  mother_dob1 = input(put(left(mother_dob),8.),yymmdd8.);

  drop mother_dob ;

  rename mother_dob1 = mother_dob;

  format mother_dob1 yymmddn8.;

run;

Since your variable is already numeric there is no need to create a new one.

data final;

  set test ;

  mother_dob = input(put(mother_dob,8.),yymmdd8.);

  format mother_dob yymmddn8.;

run;

You might also want to consider what will happen if you happen to have a smaller number (less than 8 digits) or large number (more than 8 digits) in the variable.  How clean is your data source, and given that it is Excel it is probably not very clean.

[renjithr]

Thank you all for your comments, really appreciate it!!