CODEWITHSUNDEEP
datesasformat
willd9
willd9

Reputation: 87

How do you change a variable in a CSV from text format to date format in SAS?

I'm trying to import a csv which has a variable called date. I'm trying to import it so the format once it's in a SAS table is date9 (e.g. 01MAY2021). Here is what I've tried:

Data Test;
infile "\\file.csv"
delimiter="," missover firstobs=2 dsd lrecl=32767;
informat Date ddmmyyyy.;
format  Name $100.;
format  Location $100.;
format  Date date9.;
format Type $10.;
input   Name $
        Location $
        Date 
        Type $;
run;

This currently just returns blank rows for the Date variable... The rows under the Date column in the csv are all populated as 'May-21'.

Upvotes: 2

Views: 850

Answers (3)

Tom
Tom

Reputation: 51581

Use an INFORMAT that matches the pattern of the text in the file. The MONYY informat will work with your example string.

You can then attach any date type FORMAT you want to control how the date value is printed. I wouldn't use DMY order or MDY order for dates as either choice will confuse half of the audience. You could use DATE or YYMMDD to avoid confusion. Or you could use MONYY format to get values similar to the original text. But make sure to use 4 digits for the year to avoid confusion about which century they mean.

There is no need to attach formats to your character variables. Also no need to include $ in the INPUT statement if you have already set the variable type before that statement.

data Test;
  infile "\\file.csv" dsd truncover firstobs=2;
  length Name $100 Date 8 Location $100 Type $10 ;
  informat date monyy.;
  format date monyy7. ;
  input name date location type;
run;

Upvotes: 0

Reeza
Reeza

Reputation: 21274

Since you say your date shows as May-21 which is a MONYY format (roughly) but then you use an INFORMAT of ddmmyyyy that doesn't align. Your informat should reflect how your data looks before you read it in. You can use the MONYY informat to ensure it's read correctly.

data have;
infile cards truncover;
informat orig monyy.;
format orig date9.;
input orig ;
cards;
May-21
Jun-21
Sep-20
Jan-19
;;;;
run;

proc print data=have;
run;

Results:

Obs orig
1   01MAY2021
2   01JUN2021
3   01SEP2020
4   01JAN2019

Upvotes: 0

Stu Sztukowski
Stu Sztukowski

Reputation: 12909

While I do not know what format your date variable is in for your csv file, use the informat anydtdte. to read the date. This automatically checks for a variety of different date types automatically and parses it. Use a : on your date variable in your input statement to specify the informat to use.

Data test;
    infile "\\file.csv" dlm="," missover firstobs=2 dsd lrecl=32767;

    length Name
           Location $100.
           Date     8.
           Type     $10.
    ;

    format date date9.;

    input name$ location$ date:anydtdte. type$;
run;

The ANYDTDTE informat reads input data that corresponds to any of the following informats or date, time, or datetime forms. Then, the informat extracts the date part from the derived value.

enter image description here

Upvotes: 1

Related Questions