Hi Members,
I try to import a csv file below and it really drives me crazy, especially the Time_date.
I am interested in the last 3 variables (Time_date, Bid, Ask)
Could you please help me with it?
Many thanks as always.
HHC
ID | Window | Rate | Time_date | Bid | Ask |
2.79E+09 | D | GBP/USD | 6/2/2013 17:03 | 1.5199 | 1.5202 |
2.79E+09 | D | GBP/USD | 6/2/2013 17:03 | 1.51984 | 1.52014 |
The ANYDTDTM informat was introduced with SAS9.2 please use the NLDATM informat in SAS9.1
from my code sample change the line
to
this should then give you the correct date and time.
Please note that the behavior of the NLDATM informat depends on the LOCALE= SAS System option
Hi,
Please tell us your exact problem. However I have import and attached the data set from your csv.
The following code will read your data into a SAS Data Set. I have added code to extract the date and time from time_date variable, just in case you need them to be separate
Sorry Everyone for not clarify my trouble.
Actually, I try different way to import and format but still the Time_date either does not show up or import only a fraction of the whole field of Date and time.
When I run the codes kindly posted by Bruno Muller and Scott_Mitchell, I don't have the Time_date imported. All show up is missing value!
With that, I wonder if the problem is my SAS since it is SAS 9.1.3 Portable.
Any suggestions are very much appreciated.
Thank you,
HHC
Sorry HHC, but I have tested both methods and they appear to work perfectly.
I am using SAS 9.3 on Windows 7 if that helps.
The ANYDTDTM informat was introduced with SAS9.2 please use the NLDATM informat in SAS9.1
from my code sample change the line
to
this should then give you the correct date and time.
Please note that the behavior of the NLDATM informat depends on the LOCALE= SAS System option
Thank you for your help. I got it now.
It is always a nightmare for me to import time date to SAS!
Have a nice weekend.
HHC
You haven't really told us specifically what is driving you crazy, so I have made some assumptions. Does this do what you are looking for?
DATA WORK.TEST2;
LENGTH
ID 8
Window $ 1
Rate $ 7
Time_date 8
Bid 8
Ask 8 ;
FORMAT
ID BEST8.
Window $CHAR1.
Rate $CHAR7.
Time_date DATETIME18.
Bid BEST7.
Ask BEST7. ;
INFORMAT
ID BEST8.
Window $CHAR1.
Rate $CHAR7.
Time_date DATETIME18.
Bid BEST7.
Ask BEST7. ;
INFILE 'E:\TEST2.CSV'
LRECL=49
DLM=","
MISSOVER
firstOBS=2
DSD ;
INPUT
ID : ?? COMMA8.
Window : $CHAR1.
Rate : $CHAR7.
Time_date : ?? ANYDTDTM14.
Bid : ?? COMMA7.
Ask : ?? COMMA7. ;
RUN;
Have you seen my attached dataset? Try This:
/**********************************************************************
* PRODUCT: SAS
* VERSION: 9.3
* CREATOR: External File Interface
* DATE: 25JUL13
* DESC: Generated SAS Datastep Code
* TEMPLATE SOURCE: (None Specified.)
***********************************************************************/
data TEST.TT ;
%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
infile 'E:\test\test.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
informat ID best32. ;
informat Time $1. ;
informat Rate $7. ;
informat Time_date anydtdtm40. ;
informat Bid best32. ;
informat Ask best32. ;
format ID best12. ;
format Time $1. ;
format Rate $7. ;
format Time_date datetime. ;
format Bid best12. ;
format Ask best12. ;
input
ID
Time $
Rate $
Time_date
Bid
Ask
;
if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.