BookmarkSubscribeRSS Feed
SunilCh
Calcite | Level 5


Hi,

I would like to know is there any way to convert multiple excel sheets into html directly with headder's and footnote's into sas.It should be automated.

For example  one of my excel sheet contains data like as below

BANKINDIAN NATIANAL BANK
BRANCHKUWAIT
AREACHINA
DISCRIPTIONDATA SHOLUD BE ALONG WITH GENIRIC ID”S
NOAGESALAXDF
12323441211
23
7000 7000 7000 7000 7000
2332332323323
323321332332

FOOTNOTE:asjgdfghasfd dasdgyasjdgj dasdjasjd asjdgjad.

As the above assume that, There were multiple sheets i have in one file.Plz Help me out of this...

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

Hi:

  I'm not sure why you need SAS for this task. Every since Office 97, you have been able to do a File-->Save As inside of Excel (and Word) to save your workbook or document as an HTML file. You have 2 choices with Excel -- either an HTML file or an MHT file. If you ask SAS to read the Excel sheet using PROC IMPORT or the LIBNAME engine, the data is a mixture of information. You do not have a simple structure of rows and columns. You could, for example, tell SAS to start reading data at row 5 or row 6 and to get the column headers out of row 4 or row 5, but I think that's not what you want.

  Why can't you use Excel techniques to create your HTML file?

cynthia

SunilCh
Calcite | Level 5

Hi Thanks for your reply,

Actually i need to make this requirement as automated in SAS, As you told, we can do this with Excel techniques,But my user's want to make this automated process in sas,Can you help me out of this....

Patrick
Opal | Level 21

The question is: What tells you in the Excel sheet what's header, what's body and what's footnote? Excel has simply columns and rows.

You would need to implement some kind of logic to identify the header/footer section.

I've had to do stuff like this in the past and it's ugly. My solution was to first convert the Excel to a csv and then implement some logic which determined if this was header or data section. In my case I needed to read only the data section into a SAS table, in your case you would then convert the text file to a html.

In the Excel sheet structur you show us a Proc Import with enough "guessingrow" would do as there will be at least one row (the column headings) with character values so all variables would be "character". Once you have the data in a SAS table you need to find logic to split it up into header, data and footer section and then write the data as you wish to a html.

If you have PC File Server then you could simply define a libname for your Excel workbook and by querying the sashelp.vstable it shouldn't be hard to get all the names of the sheets in this workbook.

May be the libname would already convert the Excel sheets into a format sufficient for further processing. I've made mixed experiences and it was always "try and error" for me.

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
  • 3 replies
  • 980 views
  • 0 likes
  • 3 in conversation