Reading DATE data

[Miah]

I am trying to read data that is originally read as WORDDATE, and I want to display it as MMDDYY. I have used many different informats like: DATEw, MMDDYY10., ABYDTDTE10. But instead, all I get are *******,  WORDDATE type or a numeric count of days since day 0. 

Here it is my code and I have attached the data set to this question: 

PROC IMPORT DATAFILE="/folders/myfolders/TopGrossingAlbumsR.txt"
     OUT=music
     DBMS=csv
     REPLACE;
     firstobs = 2;
     INPUT Album :$50. Artist :$50. ReleaseDate :DATETIME. TotalCertifiedCopies : ClaimedSales : Genre $ ;
     
RUN;

PROC PRINT DATA = music; 
RUN;

PROC PRINT DATA = music; 
	INFORMAT ReleaseDate MMDDYY10. ;
RUN; 

[Kurt_Bremser]

To extract the date from a datetime value, use the datepart() function.

[Reeza · Accepted solution]

You're mixing input methods here. Either you use PROC IMPORT or you use an INPUT statement within a data step but not both. 

PROC IMPORT will guess at types and you can set GUESSINGROWS=MAX to get the best attempt. 

PROC IMPORT DATAFILE="/folders/myfolders/TopGrossingAlbumsR.txt"
     OUT=music
     DBMS=csv
     REPLACE;

     getnames= yes;
     guessingrows=max;     
RUN;

Or you can try and INPUT statement within a data step:

data music_data_step;
infile '/folders/myfolders/TopGrossingAlbumsR.txt' dsd firstobs=2;
length album $50 artist $50 genre $20.;
informat releaseDate anydtdte.;
format releaseDate date9.;

input album artist releaseDate totalCertifiedCopies ClaimedSales Genre;

run;

[Tom]

You seem confused between what an INFORMAT does and what a FORMAT does. An INFORMAT is instructions for how to convert text into the value that is stored. A FORMAT is instructions for how to convert the value that is stored into text.

If you have a date variable and you want to print the value in MDY order then use the MMDDYY format.

PROC PRINT DATA = music; 
  FORMAT ReleaseDate MMDDYY10. ;
RUN; 

Of course if you want your friends in the UK to understand it then it might be better to use the YYMMDD format instead as nobody will confuse the value 2018-12-10 as meaning the twelfth of November instead of the tenth of December.

Did you try using the ANYDTDTE. informat to read your existing CSV file?

data test;
  infile cards dsd truncover firstobs=2 ;
  input Album :$50. Artist :$50. ReleaseDate :ANYDTDTE.
       TotalCertifiedCopies ClaimedSales Genre :$50.
  ;
  format ReleaseDate yymmdd10.;
cards4;
Album,Artist,ReleaseDate,TotalCertifiedCopies,ClaimedSales,Genre
Thriller,Michael Jackson,"November 30, 1982",46.3,65,Pop
Back in Black,AC/DC,"July 25, 1980",26.1,50,Rock
The Dark Side of the Moon,Pink Floyd,"March 1, 1973",24.2,45,Rock
;;;;

proc print;
run;

                                                                       Total
                                                          Release    Certified    Claimed
Obs    Album                        Artist                   Date      Copies      Sales     Genre

 1     Thriller                     Michael Jackson    1982-11-30       46.3         65      Pop
 2     Back in Black                AC/DC              1980-07-25       26.1         50      Rock
 3     The Dark Side of the Moon    Pink Floyd         1973-03-01       24.2         45      Rock