DATA Step, Macro, Functions and more

problems with length while extraction of multiple files

Accepted Solution Solved
Reply
Regular Contributor
Posts: 200
Accepted Solution

problems with length while extraction of multiple files

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;

 


Accepted Solutions
Solution
‎08-25-2017 04:29 AM
Esteemed Advisor
Posts: 5,399

Re: problems with length while extraction of multiple files

[ Edited ]

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;
PG

View solution in original post


All Replies
Super User
Posts: 22,850

Re: problems with length while extraction of multiple files

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. 

Super User
Posts: 9,586

Re: problems with length while extraction of multiple files

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎08-25-2017 04:29 AM
Esteemed Advisor
Posts: 5,399

Re: problems with length while extraction of multiple files

[ Edited ]

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;
PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 138 views
  • 1 like
  • 4 in conversation