DATA Step, Macro, Functions and more

reading a time variable

Accepted Solution Solved
Reply
Contributor
Posts: 23
Accepted Solution

reading a time variable

I'm not good at SAS time input and output procedure. I'm attaching a .csv file. I want to create a new variable named kdate1 which is kdate+1 if ktime is after 7am.

data sports;

set test1;

if hour(KTIME) < 7 then KDATE1=KDATE;

if hour(KTIME) >= 7 then KDATE1=KDATE+1;        

run;

below is the error message:

NOTE: Character values have been converted to numeric values at the places given by:


      (Line)Smiley SadColumn).


      324:13   325:13


NOTE: Invalid numeric data, KTIME='19:00' , at line 324 column 13.


NOTE: Invalid numeric data, KTIME='19:00' , at line 325 column 13.

thanks

Attachment

Accepted Solutions
Solution
‎09-29-2012 02:32 PM
PROC Star
Posts: 7,363

Re: reading a time variable

Something must have changed as proc import with 9.3 imports your file perfectly.  Apparently, 9.1 is importing the time variable as character.  You could just read it all in directly.  e.g.:

data WORK.HAVE    ;

  infile 'E:\Academic\Research\Sport\data\import1.csv' delimiter = ','

  MISSOVER DSD lrecl=32767 firstobs=2 ;

  informat DATE mmddyy10. ;

  informat KDATE mmddyy10. ;

  informat KTIME time5. ;

  informat VENUE $9. ;

  informat COMPETITION $9. ;

  informat STAGE $13. ;

  informat OPPONENT $12. ;

  informat WDL $1. ;

  informat SCORED best32. ;

  informat CONCEDED best32. ;

  informat KTIND best32. ;

  informat SPORT $15. ;

  informat WDAY best32. ;

  informat WDL1 $1. ;

  informat CHOICEIND best32. ;

  format DATE mmddyy10. ;

  format KDATE mmddyy10. ;

  format KTIME time5. ;

  format VENUE $9. ;

  format COMPETITION $9. ;

  format STAGE $13. ;

  format OPPONENT $12. ;

  format WDL $1. ;

  format SCORED best12. ;

  format CONCEDED best12. ;

  format KTIND best12. ;

  format SPORT $15. ;

  format WDAY best12. ;

  format WDL1 $1. ;

  format CHOICEIND best12. ;

  input

            DATE

            KDATE

            KTIME

            VENUE $

            COMPETITION $

            STAGE $

            OPPONENT $

            WDL $

            SCORED

            CONCEDED

            KTIND

            SPORT $

            WDAY

            WDL1 $

            CHOICEIND

  ;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 3,899

Re: reading a time variable

When you're reading your data from an external text file into SAS you must use the appropriate informat so that the text strings get converted into SAS data and SAS time values. Only then can you use date and time functions like hour().

Below some sample code which uses informat in the input statement which will do such a conversion.

data have;
  infile datalines delimiter=' ' truncover;
    INPUT
        KDATE            : MMDDYY10.
        KTIME            : TIME5.
        VENUE            : $CHAR12. ;
  format kdate date9. ktime time5.;
  datalines;
4/26/2000 19:00 Korea
9/14/2000 18:00 Australia
9/17/2000 17:30 Australia
9/17/2000 18:00 Australia
9/18/2000 11:30 Australia
9/19/2000 18:30 Australia
9/20/2000 18:00 Australia
9/20/2000 18:30 Australia
9/22/2000 10:30 Australia
9/23/2000 11:30 Australia
9/24/2000 11:30 Australia
9/26/2000 18:30 Australia
9/27/2000 11:30 Australia
10/13/2000 23:05 Lebanon
;
run;

Contributor
Posts: 23

Re: reading a time variable

I use tabs, file -import data.

How can I do informat?

I know the proc import procedure, but it's long an time-consuming.

Respected Advisor
Posts: 3,899

Re: reading a time variable

Not sure what "tabs, file -import data" means. If you're using the EG import wizard then the wizard will chose correct informats based on your data (else you could manually change it within the wizard).

Proc Import looks easy for your data (see Ksharp's post).

In a datastep: Whatever the delimiter is you can define it in the delimiter option of the infile statement. If it's a tabulator then you might have to use the hexadecimal character value to define it.

In a Windows environment this would be for a tab:  delimiter='7F'x  and for a comma separated file: delimiter=','

Super User
Posts: 9,682

Re: reading a time variable

Easy.

proc import datafile='c:\test1.csv' out=have dbms=csv replace;
run;
data have;
 set have;
 kdate1=ifn(ktime ge '07:00:00't,kdate+1,kdate);
 format kdate1 mmddyy10.;
 run;

KSharp

Contributor
Posts: 23

Re: reading a time variable

I agree that your code is easier for my case. I'm using 9.1. Somehow, I can't format the ktime variables. See the new attachment and the code below:

proc import datafile='E:\Academic\Research\Sport\data\import1.csv'
out=have dbms=csv replace;

run;

data have;

set have;

format ktime time5.;

run;

726  data have;

727   set have;

728   format ktime time5.;

                   ------

                   48

ERROR 48-59: The format $TIME was not found or could not be loaded.



729   run;

Attachment
Solution
‎09-29-2012 02:32 PM
PROC Star
Posts: 7,363

Re: reading a time variable

Something must have changed as proc import with 9.3 imports your file perfectly.  Apparently, 9.1 is importing the time variable as character.  You could just read it all in directly.  e.g.:

data WORK.HAVE    ;

  infile 'E:\Academic\Research\Sport\data\import1.csv' delimiter = ','

  MISSOVER DSD lrecl=32767 firstobs=2 ;

  informat DATE mmddyy10. ;

  informat KDATE mmddyy10. ;

  informat KTIME time5. ;

  informat VENUE $9. ;

  informat COMPETITION $9. ;

  informat STAGE $13. ;

  informat OPPONENT $12. ;

  informat WDL $1. ;

  informat SCORED best32. ;

  informat CONCEDED best32. ;

  informat KTIND best32. ;

  informat SPORT $15. ;

  informat WDAY best32. ;

  informat WDL1 $1. ;

  informat CHOICEIND best32. ;

  format DATE mmddyy10. ;

  format KDATE mmddyy10. ;

  format KTIME time5. ;

  format VENUE $9. ;

  format COMPETITION $9. ;

  format STAGE $13. ;

  format OPPONENT $12. ;

  format WDL $1. ;

  format SCORED best12. ;

  format CONCEDED best12. ;

  format KTIND best12. ;

  format SPORT $15. ;

  format WDAY best12. ;

  format WDL1 $1. ;

  format CHOICEIND best12. ;

  input

            DATE

            KDATE

            KTIME

            VENUE $

            COMPETITION $

            STAGE $

            OPPONENT $

            WDL $

            SCORED

            CONCEDED

            KTIND

            SPORT $

            WDAY

            WDL1 $

            CHOICEIND

  ;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 1100 views
  • 0 likes
  • 4 in conversation