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

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):(Column).


      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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
Patrick
Opal | Level 21

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;

econfkw
Calcite | Level 5

I use tabs, file -import data.

How can I do informat?

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

Patrick
Opal | Level 21

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=','

Ksharp
Super User

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

econfkw
Calcite | Level 5

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;

art297
Opal | Level 21

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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 6 replies
  • 3436 views
  • 0 likes
  • 4 in conversation