problems with length while extraction of multiple files

[vraj1]

i have an excel which i attached with multiple sheets in it and i am extracting them and all the sheets have same variable names.

Can any one help me in extracting as i get errors and truncated values

libname localct "M:\work/Boo.xlsx";

data test;

      set localct.'TEST$'n;

            localct.'CAT$'n;

            localct.'SCAT$'n;

            localct.'OTHER$'n;

                  where fmtname ne ' ';

run;

data study_ct;

      set test

           cat

          scat

            other

      ;

      where fmtname ne ' ';

run;

libname localct clear;

[Reeza]

Not really. Excel doesn't have a concept of fixed types per column so the data can be anything. This means when SAS imports it, it makes guesses as to the length and type. 

If all the data is being read in correctly and the error is when you try to append them, then use a LENGTH statement before your SET statement that has the desired length. 

Otherwise you're stuck first standardizing the data sets and then merging them. 

[Kurt_Bremser]

Since Excel does not have the concept of fixed attributes for columns, the XLSX engine in SAS has to make guesses for every single sheet along the contents. Different contents will therefore lead to different dataset layouts.

Possible workarounds:

- save each sheet as a csv file, and import that with a data step that sets correct attributes. Since that same code is used across all sheet files, the attributes will align correctly.

- add a ATTRIB statement before the set statement that correctly sets the required lengths and formats; if a type conversion has to be done, use a rename= dataset option to the respective in-dataset, so you can convert the values to the correct type in the data step.

Bottom line: Excel files are not useful for import of tabular data into a warehouse.

[PGStats · Accepted solution]

Assuming that your Excel file has variable names on the first line of every sheet, unlike your example, your code should look like this:

libname localct "M:\work/Boo.xlsx";
data test;
attrib Domain FmtName Start End Label type length=$100;
set localct.'TEST$'n
	localct.'CAT$'n
	localct.'SCAT$'n
	localct.'OTHER$'n;
where fmtname is not missing;
run;