DATA Step, Macro, Functions and more

Reading DATE data

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Reading DATE data

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;

 


Accepted Solutions
Solution
‎03-04-2018 10:28 PM
Super User
Posts: 22,874

Re: Reading DATE data

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;

View solution in original post


All Replies
Super User
Posts: 9,611

Re: Reading DATE data

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎03-04-2018 10:28 PM
Super User
Posts: 22,874

Re: Reading DATE data

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;
Super User
Super User
Posts: 7,860

Re: Reading DATE data

[ Edited ]

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

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 137 views
  • 3 likes
  • 4 in conversation