Help needed with import from csv file

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Help needed with import from csv file

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

IDWindowRateTime_dateBidAsk
2.79E+09DGBP/USD6/2/2013 17:031.51991.5202
2.79E+09DGBP/USD6/2/2013 17:031.519841.52014
Attachment

Accepted Solutions
Solution
‎07-25-2013 07:13 AM
SAS Super FREQ
Posts: 683

Re: Help needed with import from csv file

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.

to

    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

View solution in original post


All Replies
Occasional Contributor
Posts: 11

Re: Help needed with import from csv file

Hi,

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

Attachment
SAS Super FREQ
Posts: 683

Re: Help needed with import from csv file

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;
Super Contributor
Posts: 371

Re: Help needed with import from csv file

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

Super Contributor
Posts: 297

Re: Help needed with import from csv file

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.

Solution
‎07-25-2013 07:13 AM
SAS Super FREQ
Posts: 683

Re: Help needed with import from csv file

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.

to

    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

Super Contributor
Posts: 371

Re: Help needed with import from csv file

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

Super Contributor
Posts: 297

Re: Help needed with import from csv file

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;

Occasional Contributor
Posts: 11

Re: Help needed with import from csv file

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 332 views
  • 7 likes
  • 4 in conversation