Excel Question

[robertrao]

Hi,

The question was deleted by mistake when i tried to edit some information..

Sorry about that

Couls somebody tell me how to delete the entire post??

Regards

[art297]

: Not sure how you got what you have, but you have the time expressed as fraction of a day.  You could just convert them by multiplying by 60*60*24.  e.g.:

data have;

  input have_time;

  format time time8.;

  time=have_time*60*60*24;

  cards;

0.173611111

0.329861111

;

[art297]

: Since you never posted your SAS file, I looked at how one might import an Excel workbook of the nature that you described.  Honestly, I wouldn't know how to do it directly.  The best result I was able to attain was to save each sheet, in Excel, as a separate csv file and then input the files directly in a datastep.  The code I used was:

data may (drop=skip);

  infile "c:\art\MAY.csv" delimiter="," DSD lrecl=32767

    firstobs=2 truncover;

  informat name $30.;

  informat number $30.;

  informat date $6.;

  informat skip $30.;

  informat hours1-hours31 12.;

  informat istart1-istart31 time8.;

  format istart1-istart31 time8.;

  informat istop1-istop31 time8.;

  format istop1-istop31 time8.;

  informat tb1-tb31 $5.;

  input name skip hours1-hours31 total_hours/

    number skip istart1-istart31/

    date skip istop1-istop31/

    skip skip tb1-tb31;

run;

data june (drop=skip);

  infile "c:\art\june.csv" delimiter="," DSD lrecl=32767

    firstobs=2 truncover;

  informat name $30.;

  informat number $30.;

  informat date $6.;

  informat skip $30.;

  informat hours1-hours30 12.;

  informat istart1-istart30 time8.;

  format istart1-istart30 time8.;

  informat istop1-istop30 time8.;

  format istop1-istop30 time8.;

  informat tb1-tb30 $5.;

  input name skip hours1-hours30 skip total_hours/

    number skip istart1-istart30/

    date skip istop1-istop30/

    skip skip tb1-tb30;

run;

[Peter_C]

Art

I recognise the style of data step generated by the EFI (external file interface) for CSV ....

(i don't llke/respect it - but that's a separate discussion!)

If we were starting from scratch, I think we might make it a bit simpler.

Saving an excel sheet as a CSV file will normally retain the formatted values which really helps with dates and times and datetimes, but might be less great with ratios and percents which will save the number of decimal places that are formatted in excel.

but I hope that's not the issue here.

Having a signal in the first column that indicates the format of values on the line, will remove all the difficulties the IMPORT procedure faces here.

data like_it ;

informat  

h_jan1  8. t_jan1 time11. S_jan1 time11.

h_jan2  8. t_jan2 time11. S_jan2 time11.

h_jan3  8. t_jan3 time11. S_jan3 time11.

h_jan4  8. t_jan4 time11. S_jan4 time11.

h_jan5  8. t_jan5 time11. S_jan5 time11.

type typ2 typ3 $32.

   ;

   format  

h_jan1 8. t_jan1 time11. S_jan1 time11.

h_jan2 8. t_jan2 time11. S_jan2 time11.

h_jan3 8. t_jan3 time11. S_jan3 time11.

h_jan4 8. t_jan4 time11. S_jan4 time11.

h_jan5 8. t_jan5 time11. S_jan5 time11.

   ;

  infile "c:\art\MAY.csv" delimiter="," DSD lrecl=32767

    firstobs=2 truncover;

input type $ @ ;

if type =: 'HOUR' then input (h_: )(? : )

/  typ2 (t_:)(? : )

/ typ3  s_:)( ? : ) ;

run ;

the idea is to read 3 lines for each set of data  collecting H_{date} the number of hours, T_{date} for that time and S_{date} for the stop time

Of course, this isn't much good if that isn't the data structure , but it looks probable.

to explain / typ2 (t_:)(? : )

   /= skip to the next line

   typ2= read column store in variable typ2 with its default informat,

    (t_:)(? : ) all variables with names  beginning T_ to be read with their default informats

     ?  suppress INVALID DATA messages - for example because data is empty.

A similar data step could reproduce the input

(or proc report)

might be worth a try

peterC

[art297]

Peter,

I happen to like that the style of input I used but, as you said, that is a discussion for another day or thread.

Since this is an Excel workbook, and every four lines contain the data for each subject, I wouldn't rely on cell values to control the data that needs to be read.  Too much chance of there being variations in the headers.

As is, my proposed code only covered the vast majority of the data, as there were a number of exceptions that would require testing each cell.  E.g., the time fields may actually contain descriptions.

While the code wouldn't be very difficult, I'm not proposing it as I think Karun will only learn if he tries to write it himself and only asks further questions on this particular project if he runs into problems and has questions regarding those problems.

Art

[PGStats]

In Excel, you can give a different format to every cell, but SAS datasets are more like database tables where formats are applied to columns. In your Excel table, hours are numbers and start are times, but they must share the same format in SAS. Not possible.

SAS can import Excel time values but only if certain conditions are met.

1) The internal Excel values must be less than one (i.e. you cannot have a time value greater than 24 h)

2) The cells must be given the standard Excel time format

3) Option SCANTIME=YES must be used in the libname statement

When these conditions are met, Excel time values (expressed as fractions of days) are translated properly into SAS time values (expressed in seconds).

HTH

PG