topic Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine in SAS Programming

libname xlsx - proc datasets - copying an Excel file created with xlsx engine

xxformat_com — Tue, 02 Jun 2020 13:04:31 GMT

Hi,

If I run the following program in one go, it works properly:

*--------------------------------------------------------------------;

libname demo xlsx "&xxdemo./reporting/test.xlsx";

data demo.'All Students'n;
    set sashelp.class;
run;

data demo.'Female'n;
    set sashelp.class (where=(sex='F'));
run;

libname demo;

options validmemname=extend;

libname demo xlsx "&xxtraining./reporting/test.xlsx";

title 'DEMO';
proc print data=demo.'ALL STUDENTS'n noobs;
run;

libname demo;

libname demo xlsx "&xxtraining./reporting/test.xlsx";

proc datasets nolist nodetails;
    copy in=demo out=work;
        select 'ALL STUDENTS'n ;  
    run;
quit;

libname demo;

title 'WORK';
proc print data=work.'ALL STUDENTS'n noobs;
run;

However, it I want to run proc datasets in a second run, I get an error:

ERROR: The value ALL STUDENTS is not a valid SAS name.

ERROR: File WORK.'ALL STUDENTS'n.DATA has not been saved because copy could not be completed.

libname demo xlsx "&xxtraining./reporting/test.xlsx";

proc datasets nolist nodetails;
    copy in=demo out=work;
        select 'ALL STUDENTS'n ;  
    run;
quit;

libname demo;

title 'WORK';
proc print data=work.'ALL STUDENTS'n noobs;
run;

Are you familiar with this issue and do you how to fix it?

Best Regards,

Véronique

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

Kurt_Bremser — Tue, 02 Jun 2020 13:52:23 GMT

To make a name with a blank in it (a practice highly recommended against!) valid, you need to set the option

options validmemname=extend;

You either ran your proc datasets in a different session, or you have code that resets the option to its default.

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

xxformat_com — Tue, 02 Jun 2020 15:22:04 GMT

The option is already active in the example I gave. I'm working on interactive mode.

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

xxformat_com — Tue, 02 Jun 2020 15:26:21 GMT

ok. I see. The option does not remain active after the first run in SAS Studio. Strange.

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

Kurt_Bremser — Tue, 02 Jun 2020 16:13:18 GMT

You can find a setting in SAS Studio. Settings - Tables (or Data, I only see a German version where it is "Tabellen").

There you set the options vor VALIDVARNAME and VALIDMEMNAME, and code for this is sent before every execution. If you turn on "Show Generated Code in Log" in Settings - Code and Log, you can see the relevant code that is automatically sent in your log.

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

xxformat_com — Tue, 02 Jun 2020 16:22:49 GMT

Could you share a screenshot; it's not an issue if it is in German.

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

Kurt_Bremser — Tue, 02 Jun 2020 16:29:26 GMT

See here:

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

xxformat_com — Tue, 02 Jun 2020 16:38:37 GMT

Danke schön Herr Bremser.

In meiner Version gibt es diese Möglichkeit leider nicht.

Im General, kann ich nur validvarname ändern aber nicht validmemname.

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

Kurt_Bremser — Tue, 02 Jun 2020 17:34:27 GMT

Das müsste unter Taches sein. Welche Version des SAS Studio haben Sie? Meine ist ziemlich aktuell (University Edition).

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

xxformat_com — Tue, 02 Jun 2020 17:45:59 GMT

SAS Studio 3.7

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

Kurt_Bremser — Tue, 02 Jun 2020 17:57:31 GMT

Da wäre ein Upgrade sinnvoll. Die Option wurde mit Version 3.8 eingeführt, siehe https://support.sas.com/en/software/studio-support.html#41193f64-7919-4800-844a-370aadebfef4

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

xxformat_com — Tue, 02 Jun 2020 19:53:11 GMT

Sehr geehrter Herr Bremser,

jetzt ist bei mir SAS Studio 3.8 installiert.

Vielen Dank

Véronique

Re: libname xlsx - proc datasets - copying an Excel file created with xlsx engine

Kurt_Bremser — Tue, 02 Jun 2020 20:02:08 GMT

Na das ging ja schnell! Hauptsache, es geht jetzt.