Importing data to a structured table

[norina]

Dear Members,

May I ask for your assistance?

My first problem I am facing with is that using proc import I cannot import XLSM files. Is it even possible?

The second one is that I have to import these files in a stuctured table, but the data is scattered in the excel. Let me tell an example: to SAS column 1  -> excel E8 cell would go, column 2 -> F8, column 3 > excel E9, column 4 -> excel F9 etc...  Is it possible to specify somehow these rules? 

Thank you very much in advance for the hints!

[RW9]

Quite simply no.  First, using XLSM isn't great.  Excel is a poor choice as a data medium, and using a macro enabled workbook is even worse, including security risks and all.  You can save as from Excel and save to a CSV or something more appropriate to data storage, however...

Point 2, if there is not structure, logic or organisation to the data, then there is not logically way of creating code to read that data in.  You would need to manually work it into a usable method.  If it was me I would first flat refuse the file and tell the sender to use an appropriate data transfer file format, plus get a signed data transfer document, and then (as nobody seems to be able to do that anymore) when that doesn't happen I would start talking about large amounts of budget needed to cover the hours of manual labour getting the data usable and QC'ing such a process.

[Shmuel]

1) What sas version are you using ?

2) Is your sas on windows OS platform?

You can import .xlsm file by defining:  libname myxl '<path and file name>.xlsm';

You can relate to any sheet in your excel as if it is a sas dataset, that is:

data new;
     set myxl.<sheet name> ;
     ... any other sas statements ...
run;

you can run proc contents to verify how sas imported the xlsm sheet by:

proc contents data=myxl.<sheet-name>; run;