topic Re: Convert character to date format when importing text file in SAS Programming

Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 16:31:43 GMT

On my txt file the data in position 1 is formatted as 20020408.(yyyymmdd)

I want to import the academic_date in the same format (yyyymmdd) but as a date value in sas. How can I do this?

DATA import_file;

INFILE "K:\DATA\academic.file18.txt"

INPUT

@1 ACADEMIC_DATE $8.;

RUN;

Re: Convert character to date format when importing text file

novinosrin — Fri, 19 Oct 2018 16:35:57 GMT

why aren't you importing aka reading the date with an informat yymmdd10.?

DATA import_file;

INFILE "K:\DATA\academic.file18.txt"

INPUT

@1 ACADEMIC_DATE yymmdd10..;

format ACADEMIC_DATE yymmdd10.;

RUN;

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 17:21:21 GMT

The data is 8 characters in length. When I use the yymmdd10. I get an error message "invalid data for academic_date in line 1-9."

Re: Convert character to date format when importing text file

novinosrin — Fri, 19 Oct 2018 17:26:51 GMT

Post samples of your data so that we can test from our end. Also post the required output you want

Re: Convert character to date format when importing text file

Tom — Fri, 19 Oct 2018 17:31:08 GMT

@AP101 wrote:

The data is 8 characters in length. When I use the yymmdd10. I get an error message "invalid data for academic_date in line 1-9."

Why did you tell SAS to read 10 characters if you data is only 8 characters long?

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 18:21:20 GMT

As the length is 8 characters I used yymmdd8 and the result is 02-04-08. How can I remove get it to read as 20020408? no dashes

Re: Convert character to date format when importing text file

novinosrin — Fri, 19 Oct 2018 18:33:18 GMT

Use

yymmddn8.;

format

DATA import_file;

INFILE "K:\DATA\academic.file18.txt"

INPUT

@1 ACADEMIC_DATE yymmdd8.;

format ACADEMIC_DATE yymmddn8.;

RUN;

Re: Convert character to date format when importing text file

Tom — Fri, 19 Oct 2018 18:38:13 GMT

I don't understand the question. If you read it in using an INFORMAT it does not care what delimiter is used in the source text, the result is still a number (number of days since 1960). Then you can attach any date format you want to your variable once you have it as a date instead of as text. Personally I wouldn't use one that had month or day first as whichever order you pick will confuse half of your audience. Do use a format of DATE9. or YYMMDD10. and the values will be clearer.

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 18:43:13 GMT

That did it. Thank you.

Re: Convert character to date format when importing text file

singhsahab — Fri, 19 Oct 2018 18:44:30 GMT

Dear Ap,

you can use informat anydtdte8. and format yymmddn8. .

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 18:45:18 GMT

Using the suggestion of yymmddn8. solved it.

Re: Convert character to date format when importing text file

novinosrin — Fri, 19 Oct 2018 19:00:09 GMT

@singhsahab I disagree with the suggestion of anydtdte8. for OP's clear straight forward date value that is in the form yymmdd. Guessing informats should only be suggested when there isn't compatible one available.

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 19:18:43 GMT

@novinosrin How can I read in 2018 as a numeric date.

I've tried:

DATA import_file;

INFILE "K:\DATA\academic.file18.txt"

INPUT

@ 10 year year4.;

Re: Convert character to date format when importing text file

novinosrin — Fri, 19 Oct 2018 19:27:36 GMT

if the value is a number, it is considered standard data and so you do need a special instruction aka informat to read.

So in your example 2018 can be directly read as year value

data want;

input year;

cards;

2018

;

No need to supply any informat

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 19:33:10 GMT

i'm reading the year 2018 into sas from a text file. I want to format it as a date.

Re: Convert character to date format when importing text file

novinosrin — Fri, 19 Oct 2018 19:39:01 GMT

Sure, that doesn't make a difference whether you read from txt or csv or whateever. If your value is 2018 which is a year value and if you have logic date and month values in the same txt file, you could use mdy function to compute a sas date and format it the way you want.

What date do you think you would want to assign to just 2018?

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 19:41:10 GMT

I only want to read in 2018 from the txt file as a numeric value.

it's in position @10

Re: Convert character to date format when importing text file

novinosrin — Fri, 19 Oct 2018 19:42:14 GMT

did you try

input @10 year

Re: Convert character to date format when importing text file

AP101 — Fri, 19 Oct 2018 19:48:20 GMT

Tried:
input

@ 10 year 4.;

that worked. Thanks again.

Re: Convert character to date format when importing text file

singhsahab — Sat, 20 Oct 2018 17:49:08 GMT

@novinosrin Sir ..

I'm agree with you, but I thought it would be an alternate options.