How to import one excel tab file in SAS involving multiple sections of information/data?

0
votes

I have one excel tab with multiple sections of different data. For example, row 1-57 is a normal excel data table, with it's respective field names, but in row 61 (the blue row towards the bottom of the screenshot), a new tabular data section starts. This repeats for a couple different tabular data sections within the same excel tab. screenshot example

I would like to import this excel tab into SAS, then output each tabular data section as it's own table, I thought about using the blue row section name in order to identify that it is the start of a new section, but I'm not sure exactly how to approach this method and turn it into SAS codes.

Thanks in advance.

1
excelimportsasoutput
Regarding your latest question, there was no need to delete it as far as I can see. If the question is about differences in environment, that may well be useful to others. I don't know how the submission service works, but perhaps you submit a modified version that lets you render debugging tests, to determine what is different about the environment. - halfer

1 Answers

0
votes

Two ideas for you. First, if it's a small-ish file and you know all the section header names, you can do something like this:

data example;
input id $ var1 - var5;
datalines;
NameA   .   .   .   .   .
A1  11  11  21  24  14
A2  9   8   1   2   3
A3  4   4   3   4   3
A4  2   9   1   2   4
Total   26  32  26  32  24
 "" .   .   .   .   .
NameB   .   .   .   .   .
B1  3166    4135    1186    3775    5641
run;


data step1;
set example;
    retain tablenum 0;
    if id in ("NameA", "NameB", "NameC") then tablenum + 1;
run; 

data table1 table2 /*table... all the way to table N*/;
set step1;
 select (tablenum);
    when (1) do; output table1; end;
    when (2) do; output table2; end;

    *when (N) do; *output tableN; *end;
    end;
run;

Another thing you can do if you know where all the breaks are is to tackle it piecewise with proc import, and to use range to tell SAS where each table starts and stops (this example assumes your excel spreadsheet goes to column Z and your second table goes to line 140):

PROC IMPORT OUT= WORK.tableA DATAFILE= "C:\xlsfile.xlsx"
 DBMS=EXCEL REPLACE; Range = "SheetName$A1:Z57"; 
RUN;

PROC IMPORT OUT= WORK.tableB DATAFILE= "C:\xlsfile.xlsx"
 DBMS=EXCEL REPLACE; Range = "SheetName$A61:Z140"; 
RUN;