Q: SAS: taking data in dd.m.yyyy format from a csv

1
votes

I need to import data from a csv-file. And I'm able to read everything else but the date. The date format is like dd.m.yyyy format: 6;Tiku;17.1.1967;M;191;

I'm guessing if I need to specify an informat to read it in? I can't figure out which one to use because nothing I've tried works.

What I've managed so far:

data [insert name here];
  infile [insert name here];
  dlm=";" missover;
  length Avain 8 Nimi $10 Syntymapaiva 8 Sukupuoli $1 Pituus 8 Paino 5;
  input 
    Avain Nimi $ Syntymapaiva ddmmyyp.(=this doesnt work) Sukupuoli$ Pituus                 
  Paino;
  format  Paino COMMA5.2 ;
  label Syntymapaiva="Syntymäpäivä";
run;

And part of the actual file to read in:

 6;Tiku;17.1.1967;M;191;

Thank you for helping this doofus out!

2
datesasinformat

2 Answers

1
votes

There is no informat named DDMMYYP.. Use the informat DDMMYY. instead.

Also make sure to use the : modifier before the informat specification included in the INPUT statement so that you are still using list mode input instead of formatted input. If you use formatted input instead of list mode input then SAS could read past the delimiter.

input Avain Nimi Syntymapaiva :ddmmyy. Sukupuoli Pituus Paino;

Perhaps you are confused because there is a format named DDMMYYP.

Formats are used to convert values to text. Informats are what you need to use when you want to convert text to values.

553  options nofmterr ;
554  data _null_;
555    str='17.1.1967';
556    ddmmyy  = input(str,ddmmyy10.);
557    ddmmyyp = input(str,ddmmyyp10.);
                           ----------
                           485
NOTE 485-185: Informat DDMMYYP was not found or could not be loaded.

558    put str= (dd:) (= yymmdd10.);
559    _error_=0;
560  run;

NOTE: Invalid argument to function INPUT at line 557 column 13.
str=17.1.1967 ddmmyy=1967-01-17 ddmmyyp=.
NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to
      missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 557:13
1
votes

You could use the anydtdte informat, but (as @Tom points out) if your data is known to be fixed in this format, then ddmmyy. would be be better. Also, Tom's advice about using the : modifier is correct, and is preferable to use in most (if not all) cases. 

data want;
  infile cards dlm=";" missover;
  input Avain Nimi:$10. Syntymapaiva:ddmmyy. Sukupuoli:$1. Pituus Paino;
  format  Paino COMMA5.2 Syntymapaiva date9.;
  label Syntymapaiva="Syntymäpäivä";
datalines4;
6;Tiku;17.1.1967;M;191;
;;;;
run;

which gives:

enter image description here