topic Re: Extract year from a CSV file with different formats in New SAS User

Extract year from a CSV file with different formats

PeterMiller13 — Wed, 12 Jun 2024 18:28:12 GMT

Hey SAS Community,

I am new to SAS and I have a Problem with extracting the year from a csv-file with multiple dateformats.

The field 'date' in my csv file has formats like:

dd-mm-yyyy like 20-03-2014 or 01-10-2015

dd/mm/yyyy like 10/10/2014

d/mm/yyyy like 1/10/2013

dd/m/yyyy like 13/4/2010

d/m/yyyy like 3/3/2012

I tried to solve it in different ways and searched in different forums, unfortunately I am not able to solve the problem.

Any help would be very appreciated.

Best Greetings,

Peter

Edit: Whats also is weird in my txt-editor i can see the different formats, but on sas's result view it shows only one format.

Re: Extract year from a CSV file with different formats

antonbcristina — Wed, 12 Jun 2024 18:21:57 GMT

Hi @PeterMiller13, have you tried the anydtdte. informat? Works wonders, here's the documentation: https://documentation.sas.com/doc/en/leforinforref/3.2/n04jh1fkv5c8zan14fhqcby7jsu4.htm

Re: Extract year from a CSV file with different formats

PeterMiller13 — Wed, 12 Jun 2024 18:23:05 GMT

Yes i tried, it did not work.
Thank you for your answer!

Re: Extract year from a CSV file with different formats

Tom — Wed, 12 Jun 2024 18:32:14 GMT

I am not at all sure what you mean. But the DDMMYY informat should read the example you show fine to create actual DATE values. You can then use the YEAR() function to get the YEAR from the DATE.

Example:

First let's make an example CSV file we can read from:

filename csv temp;
options parmcards=csv;
parmcards;
row,date
1,20-03-2014
2,01-10-2015
3,10/10/2014
4,1/10/2013
5,13/4/2010
6,3/3/2012
;

Now let's read it in and calculate the YEAR. Let's format the DATE variable with a format that will display the date in an unambiguous style.

data want;
  infile csv dsd firstobs=2 truncover;
  input row date :ddmmyy.;
  year=year(date);
  format date yymmdd10.;
run;

Result

Obs    row          date    year

 1      1     2014-03-20    2014
 2      2     2015-10-01    2015
 3      3     2014-10-10    2014
 4      4     2013-10-01    2013
 5      5     2010-04-13    2010
 6      6     2012-03-03    2012

Re: Extract year from a CSV file with different formats

data_null__ — Wed, 12 Jun 2024 18:38:26 GMT

If I understand correctly you want to remove the leading zero from day and month for the formatted value?

Re: Extract year from a CSV file with different formats

Tom — Wed, 12 Jun 2024 18:42:53 GMT

Edit: Whats also is weird in my txt-editor i can see the different formats, but on sas's result view it shows only one format.

Not sure what that means.

If you are looking at a printout from a SAS dataset in the RESULTS viewer (of whatever interface you are using to run SAS) then it should use the same format to display the value of a single variable. That is what makes a dataset different from a text file (like a CSV file) or a spreadsheet (like an Excel file). The only way to have each date value look different is to not make dates out of them, but instead just read them in as a character variable.