Desktop productivity for business analysts and programmers

No data set being generated from an Excel file Import

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

No data set being generated from an Excel file Import

We have upgraded a short time ago from EG 6.1 to 7.1.

I just tried, as I have previoulsy done successfully, importing an Excel file (previoulsy tested) and I learned that if I import it the first time it will generate a data set.

If I close out the app of project and try a second time it will not generate a data set. It will only show a Log and code windows with a Log summary without any errors only notes.


Accepted Solutions
Solution
‎05-06-2015 10:33 PM
SAS Super FREQ
Posts: 271

Re: No data set being generated from an Excel file Import

Hi Bill,

I am able to reproduce the problem in EG 7.1.  It only occurs if the output data set name contains extended characters and it already exists.  From your log (and my repro steps), the output data set is not being properly quoted the second time.  For example, the generated code looks like...

DATA WORK.NCRF MEMBERSHIP FILE TYPE L_0000;

...which is invalid code, since the member name contains extended characters (spaces), but is not quoted.  Valid code (properly quoted member name) would look like...

DATA WORK.'NCRF MEMBERSHIP FILE TYPE L_0000'n;

I can think of a few ways to workaround...

1. Explicitly change the output data set name on the first page of the Import Wizard, so that it doesn't contain extended characters (ex. replace the spaces with underscores),

2. Delete the already existing imported/output table before importing it the second time.

3. Rename the .xls file so that it doesn't contain extended characters (remove the spaces).

I'll work with tech support to make sure this is tracked and fixed.

Casey

View solution in original post


All Replies
Valued Guide
Posts: 854

Re: No data set being generated from an Excel file Import

I'm not sure without seeing the log but it sounds like you are importing something that is already in your work library.  If this is the case it will not generate a dataset but you have access to it in your work library.  Either expand your libraries  and go to your work folder, there you can double click, or you can write the code that will generate it. I would only do this if it is a small dataset.

data test;

set 'name here';

run;

Contributor
Posts: 42

Re: No data set being generated from an Excel file Import

Mark;

Thanks for the reply.

I was able to do this procedure in e.g. 6.1 without a proble.

I've reached out to SAS Tech Support who have a copy of the Log & Code which I also thought that I attached to this original discussion.

The data set is very small.

Valued Guide
Posts: 854

Re: No data set being generated from an Excel file Import

Can you view the dataset using either of the two options I suggested?

Solution
‎05-06-2015 10:33 PM
SAS Super FREQ
Posts: 271

Re: No data set being generated from an Excel file Import

Hi Bill,

I am able to reproduce the problem in EG 7.1.  It only occurs if the output data set name contains extended characters and it already exists.  From your log (and my repro steps), the output data set is not being properly quoted the second time.  For example, the generated code looks like...

DATA WORK.NCRF MEMBERSHIP FILE TYPE L_0000;

...which is invalid code, since the member name contains extended characters (spaces), but is not quoted.  Valid code (properly quoted member name) would look like...

DATA WORK.'NCRF MEMBERSHIP FILE TYPE L_0000'n;

I can think of a few ways to workaround...

1. Explicitly change the output data set name on the first page of the Import Wizard, so that it doesn't contain extended characters (ex. replace the spaces with underscores),

2. Delete the already existing imported/output table before importing it the second time.

3. Rename the .xls file so that it doesn't contain extended characters (remove the spaces).

I'll work with tech support to make sure this is tracked and fixed.

Casey

Esteemed Advisor
Posts: 6,646

Re: No data set being generated from an Excel file Import

Make your life easier and adhere to the validvarname=v7 principles.

Especially follow the "no blanks in filenames / pathnames" rule. Blanks in names are just a major PITA easily avoided.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 32

Re: No data set being generated from an Excel file Import

Blanks in filenames may be replaced with underscores. These cause other problems, e.g. if the files are on SharePoint system the names will not be wrapped at the underscore, which can create unreadable displays of long file names. Modern software should be able to deal with any valid filenames, including spaces and accented characters, without jumping through hoops. Internationalisation requires this. This appears to be a regression bug in SAS.

Contributor
Posts: 42

Re: No data set being generated from an Excel file Import

Thanks Peter!

That's is exactly the case in tis situation.

Lessons learned!

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 603 views
  • 7 likes
  • 5 in conversation