Import date in "mm/yyyy" format

Reply
Contributor
Posts: 27

Import date in "mm/yyyy" format

I need to import date information currently in the form "MM/YYYY." I want the data imported into a sas date field.

Ex:

05/2012

07/2012

becomes

05/01/2012

07/01/2012

Thanks!

Super User
Super User
Posts: 7,076

Re: Import date in "mm/yyyy" format

You can use the ANYDTDTE. informat.

data want ;

  input dt anydtdte.;

  put dt= dt date9.;

cards;

05/2001

run;

Contributor
Posts: 27

Re: Import date in "mm/yyyy" format

I get the message: The informat $ANYDTDTE was not found or could not be loaded. This message does not happen with your example. Here's my code:

data newimport;

     infile "&myfile" delimiter = "|" MISSOVER DSD firstobs=1;

     informat Identifier $20.;

     informat Maturity_Date $20.;

     format Identifier $20.;

     format Maturity_Date $20.;

  input

     Identifier $

     Maturity_Date ANYDTDTE.

;

run;

Super User
Posts: 19,869

Re: Import date in "mm/yyyy" format

You're trying to apply a date format to a character variable, remove the $ sign for the format/informat for the maturity_date variable.

Contributor
Posts: 27

Re: Import date in "mm/yyyy" format

When I remove the $, no values are imported.

Contributor
Posts: 27

Re: Import date in "mm/yyyy" format

Ok, This works:

data newimport;

     infile "&myfile" delimiter = "|" MISSOVER DSD firstobs=1;

     informat Identifier $20.;

     informat Maturity_Date $20.;

     format Identifier $20.;

     format Maturity_Date $20.;

  input

     Identifier $

     Maturity_Date ;

maturity_date = input(maturity_date, anydtdte.);

run;

Super User
Super User
Posts: 7,076

Re: Import date in "mm/yyyy" format

Why are you applying informats like $20. to variables?  If you want to define the length of the variable using a LENGTH or ATTRIB statement will be clearer.

Dates are not character variables. Use TRUNCOVER instead of MISSOVER option on the INFILE statement.  There is no need to specify a format in the INPUT statement if there is one already defined for the variable will work.

data newimport;

  infile cards delimiter = "|" TRUNCOVER DSD firstobs=1;

  length Identifier $20 Maturity_Date 8 ;

  format Maturity_Date mmyys7. ;

  informat Maturity_Date anydtdte. ;

  input Identifier Maturity_Date ;

  put (_all_) (=) _infile_ ;

cards;

x1|05/2013

x2|06/2012

run;

Identifier=x1 Maturity_Date=05/2013 x1|05/2013

Identifier=x2 Maturity_Date=06/2012 x2|06/2012

Contributor
Posts: 27

Re: Import date in "mm/yyyy" format

I don't care to explicity define variable length. Thats what a delimited file is for. Just put my data in variables that fit.

This code fits my purpose, though its probably horribly inefficient. If there's a way to reduce from 2 steps into one that would be great, but I can't figure out how to read in a character date value, then convert it to my desired numerical date format all in one step. Maybe I should add a drop on Maturity_date as well, and a rename on Maturity_date2? This is all garbled, and not the best, but it gives me the resulting dataset that is correct.

data newimport;

     infile "&myfile" delimiter = "|" MISSOVER DSD firstobs=1;

     informat Identifier $20.;

     informat Maturity_Date $20.;

     format Identifier $20.;

     format Maturity_Date $20.;

  input

     Identifier $

     Maturity_Date ;

maturity_date = input(maturity_date, anydtdte.);

run;

data newimport;

set newimport;

format Maturity_date2 date9.;

maturity_date2 = maturity_date * 1;

run;

Super User
Posts: 19,869

Re: Import date in "mm/yyyy" format

You need to specify your informats and formats appropriately, try the code below.

If you don't want a date value, ie 21Jan2011, you can use format 12. instead or any other numeric one to get it into a number instead of multiplying by 1.

data newimport;

     infile "&myfile" delimiter = "|" MISSOVER DSD firstobs=1;

     informat Identifier $20.;

     informat Maturity_Date anydtdte.;

     format Identifier $20.;

     format Maturity_Date date9.;

  input

     Identifier $

     Maturity_Date ;

run;

Contributor
Posts: 27

Re: Import date in "mm/yyyy" format

Ok, that works great. I think I tried something similar earlier, but had:

format Maturity_Date anydtdte.;

instead of

format Maturity_Date date9.;

Makes sense I had an error. I didn't realize anydtdte was only an informat, and not a format.

Thanks for your help!

Super User
Super User
Posts: 7,076

Re: Import date in "mm/yyyy" format

You are creating a SAS dataset from your delimited file, so you do need to define the lengths of the variables.  SAS will define the variable types and lengths as soon as it can figure it out from reading your code.  If you give it no other information then SAS will use length of 8 for numbers (all SAS numbers are floating point) and $8 for character variables (all SAS character variables are fixed length and padded on the right with spaces).  There is no useful purpose to assigning informats or formats like $20. to variables (and there is a potential for unwanted headaches down the line if you do) but it will get SAS to ASSUME that you want to make the variable that length.  It is simpler to just tell it what length you want to use for the variable and avoid the side effect of having $xx. formats/informats permanently attached to the variable.

To combine it into one step just move the definition of the new variable into the first data step.

data newimport;

  infile "&myfile" delimiter = "|" TRUNCOVER DSD firstobs=1;

  length Identifier $20 Maturity_Date $20 Maturity_Date2 8;

  format Maturity_Date2 date9. ;

  input Identifier Maturity_Date ;

  Maturity_Date2 = input( Maturity_Date, anydtdte. );

run;

Super User
Posts: 11,343

Re: Import date in "mm/yyyy" format

ANY date format is going to result in similar behavior due to the way that SAS stores DATE variables. If you don't want to see the imputed day of the month make sure to use a corresponding display variable.

Or put up with not being able to use any of the date manipulation functions and leave as character.

Ask a Question
Discussion stats
  • 11 replies
  • 1616 views
  • 0 likes
  • 4 in conversation