Good morning,
I have a scv file with time of the format hh:mm:ss AM. I try different format but still fail to import it to SAS.
All the time, missing data for the whole column.
Any help is very much appreciated.
Thank you,
HHC
Data
2006.01.30 12:00:00 AM
2006.12.30 12:00:00 AM
2006.12.25 12:00:00 AM
The code I use right now is:
data originaldata;
infile 'C:\test.csv'
delimiter=',';
input date :yymmdd8. time ;
format date yymmdd8.;
format time ___________;
run;
You should be able to read that with TIME. informat.
Try it this way.
data want;
infile cards dsd dlm=',' truncover ;
informat date yymmdd10. time time11. ;
input date time;
format date date9. time time8. ;
put date time;
cards;
2005.06.28,00:00
2005.06.29,1:00
2005.06.30,5:00
run;
Some things check if does not work for you.
The TIME informat will read the AM/PM, if you tell it to read enough characters.
data want;
input date yymmdd10. time time11. ;
format date date9. time time8. ;
put date time;
cards;
2006.01.30 12:00:00 AM
2006.12.30 11:00:00 PM
2006.12.25 12:00:00 PM
run;
Thanks, Tom.
When I try your code as below, it still doesn't work. All missing on Time column.
One thing in csv file is that: the time appear on screen is 0:00 for 12:00:00 AM, 1:00 for 1:00:00 AM. I see the full minutes and second (1:00:00 AM) when I click on that cell.
So I don't know if it is something that cause my trouble.
HHC
data originaldata;
infile 'C:\test.csv'
delimiter=',';
input date :yymmdd8. time time11.;
format date yymmdd8.;
format time time8.;
run;
CSV files do not have "cells". Are you sure that it is two fields in the CSV file? That is, do you see a comma between the date and time strings? If you are having trouble seeing the actual text of the CSV then open it in the SAS editor or some other text editor or add a LIST statement to your data step..
You might need to read it as a character variable and then generate the date and time from that.
data want;
infile cards dsd dlm=',' truncover ;
input dt :$22. ;
date = input(scan(dt,1,' '),yymmdd10.);
time = input(substr(dt,length(scan(dt,1,' '))+2),time11.);
format date date9. time time8. ;
put date time;
cards;
2006.01.30 12:00:00 AM
2006.12.30 11:00:00 PM
2006.12.25 12:00:00 PM
run;
I just get back and check.
The file type is: Microsoft Excel Comma Separated Value File.
When I open it, it opens in Excel and therefore I see the
" time appear on screen is 0:00 for 12:00:00 AM, 1:00 for 1:00:00 AM. I see the full minutes and second (1:00:00 AM) when I click on that cell."
To check if it has comma, I right click the file and chose to OPEN with Notepad. Then the data look like that.
So look like it depends on the software I use to open, I see different things.
2005.06.28,00:00
2005.06.29,1:00
2005.06.30,5:00
In this case what should I do?
Thank you, Tom.
HHC
You should be able to read that with TIME. informat.
Try it this way.
data want;
infile cards dsd dlm=',' truncover ;
informat date yymmdd10. time time11. ;
input date time;
format date date9. time time8. ;
put date time;
cards;
2005.06.28,00:00
2005.06.29,1:00
2005.06.30,5:00
run;
Some things check if does not work for you.
Hi Tom,
Thank you so much for your help.
It works nicely.
Have a nice day.
HHC
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.