topic reading reversed date format in SAS Programming

reading reversed date format

Samero — Tue, 07 Jul 2020 14:04:15 GMT

Hallo,

I have a data in which the date is reversly written

Farmnumber	Date	Animalearnumber	Diagnose
90503537	20200401	276001304727874	1.10.07.10.
90503537	20200401	276001304016422	1.10.07.03.
90503537	20200401	276001304016422
90503537	20200401	276001305669949
90503537	20200401	276001305669949	1.10.07.10.
90503537	20200401	276001304727330	1.10.07.06.
90503537	20200401	276001304016088
90503537	20200401	276001304016088

how can I tell sas to read the date in this format and convert it to DDMMYYP10. format

Thank You in advance.

Re: reading reversed date format

PeterClemmensen — Tue, 07 Jul 2020 14:13:45 GMT

What date does 20200401 represent?

Re: reading reversed date format

Kurt_Bremser — Tue, 07 Jul 2020 14:36:50 GMT

See this:

data want;
infile datalines dlm=" " dsd truncover;
input Farmnumber :$8. Date :yymmdd8. Animalearnumber :$15.  Diagnose :$15.;
format date ddmmyyp10.;
datalines;
90503537 20200401 276001304727874 1.10.07.10.
90503537 20200401 276001304016422 1.10.07.03.
90503537 20200401 276001304016422  
90503537 20200401 276001305669949  
90503537 20200401 276001305669949 1.10.07.10.
90503537 20200401 276001304727330 1.10.07.06.
90503537 20200401 276001304016088  
90503537 20200401 276001304016088
;

Re: reading reversed date format

Samero — Tue, 07 Jul 2020 14:40:04 GMT

01.04.2020

Re: reading reversed date format

Samero — Tue, 07 Jul 2020 14:50:19 GMT

Many thanks, can"t I do this without reentering the Datalines because it is avery long Data and it is in one Excel file, I want to import the excel file and at the same time read the date in a correct form.
eventhough i have tried what you have suggested and it didn"t work

Re: reading reversed date format

Kurt_Bremser — Tue, 07 Jul 2020 14:57:35 GMT

So the first thing you do is to save the data to a reasonable file format (Excel files are an utter pain in the ass to import), and then apply my data step with some slight changes.

Save the file as .csv from Excel, and then run:

data want;
infile "path_to_your_file" dlm=";" dsd truncover;
input Farmnumber :$8. Date :yymmdd8. Animalearnumber :$15.  Diagnose :$15.;
format date ddmmyyp10.;
run;

DATALINES act (mostly) like any other INFILE, only that the data is written in-line into the DATA step.

Re: reading reversed date format

Reeza — Tue, 07 Jul 2020 15:54:42 GMT

When you import your Excel file what is the type and format on the variable?
If it's numeric with a date format, you just need to change the format and can do that with a format statement. If it's a character variable then you'll have to convert it using a data step or SQL.

Option 1)
format date ddmmyyp10.;

Option 2)

data want;
set importedFromExcel;
date_want = input(date, yymmdd8.);
format date_want ddmmyyp10.;
run;

Re: reading reversed date format

smantha — Tue, 07 Jul 2020 17:17:00 GMT

You cannot tell proc import on how to read the data as proc import does it's own interpretation of the column.

Here is a mashup of all the ides given so far

1. Read excel file

2. if date is numeric in a subsequent data step:

New_date=input(put(date,$8.),yymmdd8.);

format new_date mmddyyp10.;

if date is a character

New_date=input(date,yymmdd8.);

format new_date mmddyyp10.;

Re: reading reversed date format

Samero — Wed, 08 Jul 2020 11:11:29 GMT

wow, that worked like a magic, many thanks, really appreciate

Re: reading reversed date format

Samero — Wed, 08 Jul 2020 12:23:59 GMT

only the Animalearnumber was not read correctly, it shows 2,76E+14 and they can not be converted to number format

Re: reading reversed date format

Tom — Wed, 08 Jul 2020 12:56:33 GMT

@Samero wrote:
only the Animalearnumber was not read correctly, it shows 2,76E+14 and they can not be converted to number format

Do not store that string as a NUMBER. You are not going to do arithmetic on that value. And there are limits on the number of decimal digits that can be stored. SAS uses floating point numbers so the limit is about 15 decimal digits. Just store that value as character.

If the values are not larger than the maximum contiguous integer that SAS can represent exactly then you can just change the format used to display the value.

format Animalearnumber 15. ;

Side note. Does SAS really display scientific notation using comma for decimal point instead of period? Perhaps based on your LOCALE setting? Or are you showing how the value looks in Excel or some other tool?

Re: reading reversed date format

Samero — Wed, 08 Jul 2020 13:12:44 GMT

yes it shows like this 2,76001E+14 (Local setting Europe),
when I save my Excel file as CSV format the Animalearnumber turned to be like that, I don2t know why though i tried both Standard and Number format for the column in excel befor saving it as csv

Re: reading reversed date format

Tom — Wed, 08 Jul 2020 13:33:38 GMT

That is an Excel issue. First make sure the field in entered properly into the original Excel file and properly formatted in Excel. Examples:

The possible issue is how you are looking at the text file. You should open the text file with a text editor and not with Excel. If you do open the text file with Excel DO NOT let Excel just auto open it. If you want to look at it with Excel make sure you take the time to tell Excel how to read each column from the file.

Re: reading reversed date format

Samero — Wed, 08 Jul 2020 13:36:01 GMT

that was very usefull, thanks a lot

Re: reading reversed date format

Samero — Wed, 08 Jul 2020 14:33:47 GMT

"The possible issue is how you are looking at the text file.", yes this seems to be the issue, actualy I have notced now that all the data which I read in SAS they shows the same symbols, i think it is normal and nothing wrong with that,
appreciate your answer
Best Regards

Re: reading reversed date format

Kurt_Bremser — Wed, 08 Jul 2020 17:14:29 GMT

@Samero wrote:
only the Animalearnumber was not read correctly, it shows 2,76E+14 and they can not be converted to number format

That's why I read it as character with a $15. informat.