BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

3 REPLIES 3
Reeza
Super User

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
Super User

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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