BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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
1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

8 REPLIES 8
Aniket
Calcite | Level 5

Hi,

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

BrunoMueller
SAS Super FREQ

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;
hhchenfx
Barite | Level 11

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

Scott_Mitchell
Quartz | Level 8

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.

BrunoMueller
SAS Super FREQ

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

hhchenfx
Barite | Level 11

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

Scott_Mitchell
Quartz | Level 8

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;

Aniket
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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