Help using Base SAS procedures

Excel Question

Reply
Super Contributor
Posts: 1,041

Excel Question

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

PROC Star
Posts: 7,468

Re: Excel Question

Posted in reply to robertrao

: 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

;

PROC Star
Posts: 7,468

Re: Excel Question

Posted in reply to robertrao

: 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;

Valued Guide
Posts: 2,177

Re: Excel Question

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_Smiley Happy(? : )

/ typ3  s_Smiley Happy( ? : ) ;

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_Smiley Happy(? : )

   /= skip to the next line

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

    (t_Smiley Happy(? : ) 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

PROC Star
Posts: 7,468

Re: Excel Question

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

Respected Advisor
Posts: 4,920

Re: Excel Question

Posted in reply to robertrao

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

PG
Ask a Question
Discussion stats
  • 5 replies
  • 421 views
  • 6 likes
  • 4 in conversation