topic Re: Help needed with import from csv file in SAS Programming

Help needed with import from csv file

hhchenfx — Thu, 25 Jul 2013 04:25:37 GMT

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

Re: Help needed with import from csv file

Aniket — Thu, 25 Jul 2013 05:05:49 GMT

Hi,

Please tell us your exact problem. However I have import and attached the data set from your csv.

Re: Help needed with import from csv file

BrunoMueller — Thu, 25 Jul 2013 06:43:16 GMT

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

filename mycsv "c:\temp\test.csv"; 
data test;
  infile mycsv dlm="," firstobs=2; 
  input
    ID : 8.
    Time : $1.
    Rate : $12.
    Time_date : anydtdtm.
    Bid : 8.
    Ask : 8.
  ;
  justDate = datepart(time_date);
  justTime = timepart(time_date);
  format
    time_date datetime19.
    justDate date9.
    justTime time8.
    bid ask 10.5
  ;
run; 
filename mycsv clear;

Re: Help needed with import from csv file

Scott_Mitchell — Thu, 25 Jul 2013 06:52:17 GMT

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;

Re: Help needed with import from csv file

hhchenfx — Thu, 25 Jul 2013 08:50:14 GMT

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

Re: Help needed with import from csv file

Aniket — Thu, 25 Jul 2013 09:04:28 GMT

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

Time $

Rate $

Time_date

Bid

Ask

;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */

run;

Re: Help needed with import from csv file

Scott_Mitchell — Thu, 25 Jul 2013 09:36:08 GMT

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.

Re: Help needed with import from csv file

BrunoMueller — Thu, 25 Jul 2013 11:13:21 GMT

The ANYDTDTM informat was introduced with SAS9.2 please use the NLDATM informat in SAS9.1

from my code sample change the line

    Time_date : anydtdtm.

    Time_date : nldatm.

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

Re: Help needed with import from csv file

hhchenfx — Thu, 25 Jul 2013 13:38:37 GMT

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