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;
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;
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.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: