Will the following run in a Window's server or EG environment?

Accepted Solution Solved
Reply
Esteemed Advisor
Posts: 7,295
Accepted Solution

Will the following run in a Window's server or EG environment?

A couple of weeks ago I asked for help comparing the exportxl macro with other methods for exporting Excel workbooks. The result was that the macro worked extremely well on stand-alone window's environments, but either worked slowly or failed to work altogether in EG and server environments.

I don't think the macro's performance can be improved for non-stand-alone environments, but I'd like to see if the changes I've made allow it to at least work where the previous version had failed. The macro uses VB script, thus is probably limited to Windows-based systems.

If you are on a server based Window's system would you please try run the following and let me know if it works? The two parameter settings you may have to change are the outfile and server_path parameters (in the call to the macro at the bottom of the following code) as the paths they describe have to exist (with write access) on your server.

Thanks in advance,

Art

%macro exportxl(data=,

                outfile=,

                sheet=,

                type=N,

                usenames=Y,

                range=A1,

                replace=Y,

                template=,

                templatesheet=,

                useformats=N,

                method=I,

                server_path=);

/*Check whether the data and out parameters contain one or two-level filenames*/

  %let lp=%sysfunc(findc(%superq(data),%str(%()));

  %if &lp. %then %do;

    %let dsoptions=%sysfunc(substrn(&data.,&lp));

    %let data=%sysfunc(substrn(&data.,1,%eval(&lp-1)));

  %end;

  %else %let dsoptions=;

  %if %sysfunc(countw(&data.)) eq 2 %then %do;

    %let libnm=%scan(&data.,1);

    %let filenm=%scan(&data.,2);

  %end;

  %else %do;

    %let libnm=work;

    %let filenm=&data.;

  %end;

  %if %length(&outfile.) lt 1 %then

    %let outfile=%sysfunc(pathname(&libnm.))\&filenm..xlsx;;

  %if %length(&sheet.) lt 1 %then

    %let sheet=&filenm.;;

   

  %if %upcase(&type.) eq P %then %do;

    proc export

      data=&libnm..&filenm. &dsoptions.

      outfile= "&outfile."

      dbms=excel

      %if %upcase(&replace.) eq Y %then replace;

      ;

      %if &sheet. ne "" %then sheet="&sheet.";;

    run;

  %end;

  %else %do;

    %if %upcase(&range.) eq Y %then %do;

      data _null_;

        window range rows=8 columns=80

        irow=1 icolumn=2 color=black

        #2 @3 'Enter the upper left cell where range should begin (e.g. D5): '

        color=gray range $41. required=yes

        attr=underline color=yellow;

        DISPLAY range blank;

        call symputx('range',range);

        stop;

      run;

    %end;

    %else %if %length(&range.) lt 2 %then %do;

      %let range=A1;

    %end;

    %if %upcase(&template.) eq Y %then %do;

      data _null_;

        window template rows=8 columns=80

        irow=1 icolumn=2 color=black

        #2 @3 'Enter the template path and name: '

        color=gray template $41. required=yes

        attr=underline color=yellow;

        DISPLAY template blank;

        call symputx('template',template);

        stop;

      run;

    %end;

    %else %if %length(&template.) lt 2 %then %do;

      %let template=;

    %end;

    %if %upcase(&templatesheet.) eq Y %then %do;

      data _null_;

        window templatesheet rows=8 columns=80

        irow=1 icolumn=2 color=black

        #2 @3 "Enter the template sheet's name: "

        color=gray templatesheet $41. required=yes

        attr=underline color=yellow;

        DISPLAY templatesheet blank;

        call symputx('templatesheet',templatesheet);

        stop;

      run;

    %end;

    %else %if %length(&templatesheet.) lt 2 %then %do;

      %let templatesheet=;

    %end;

    data _null_;

      dsid=open(catx('.',"&libnm.","&filenm."));

      if dsid eq 0 then do;

        rc=close(dsid);

        link err;

      end;

      rc=close(dsid);

      err:

      do;

        m = sysmsg();

        put m;

        stop;

      end;

    run;

    data t_e_m_p;

      set &libnm..&filenm. (obs=1);

    run;

    data t_e_m_p;

      set t_e_m_p &dsoptions.;

    run;

    proc sql noprint;

      select name,length,type,format,label

        into :vnames separated by "~",

             :vlengths separated by "~",

             :vtypes separated by "~",

             :vformats separated by "~",

             :vlabels separated by "~"

          from dictionary.columns

            where libname="WORK" and

                  memname="T_E_M_P"

      ;

    quit;

    %let nvar=&sqlobs.;

    filename code2inc temp;

    data _null_;

      file code2inc;

      length script $80;

      length fmt $32;

      do i=1 to &nvar;

        if i gt 1 then put 'rc=fput(fid,"09"x);';

        %if %upcase(&useformats.) eq Y %then %do;

          fmt=scan("&vformats.",i,"~","M");

        %end;

        %else call missing(fmt);;

        if scan("&vtypes.",i,"~") eq 'char' then do;

          if missing(fmt) then

           fmt=catt('$',scan("&vlengths.",i,"~","M"),'.');

          script=catt('rc=fput(fid,putc(put(',

           scan("&vnames.",i,"~","M"),',',fmt,"),'$char",

           scan("&vlengths.",i,"~","M"),".'));");

          put script;

        end;

        else do;

          if missing(fmt) then fmt='best32.';

          script=catt('rc=fput(fid,putc(put(',

           scan("&vnames.",i,"~","M"),',',fmt,"),'$char32.'));");

          put script;

        end;

      end;

      put 'rc=fwrite(fid);';

    run;

    data _null_;

      dsid=open("work.t_e_m_p");

      rc=attrn(dsid,'any');

      if rc ne 1 then do;

        rc=close(dsid);

        link err;

      end;

      rc=close(dsid);

      err:

      do;

        m = sysmsg();

        put m;

        stop;

      end;

    run;

    data _null_;

      %if %length(&server_path.) gt 2 %then %do;

        rc=filename('clippy',"&server_path.\clip.txt",'DISK');

      %end;

      %else %do;

        rc=filename('clippy',' ','clipbrd');

      %end;

      if rc ne 0 then link err;

      fid=fopen('clippy','o',0,'v');

      if fid eq 0 then link err;

      do i = 1 to &nvar.;

        %if %upcase(&usenames.) ne N %then %do;

          if i gt 1 then rc=fput(fid,'09'x);

          %if %upcase(&usenames.) eq Y %then %do;

            rc=fput(fid,scan("&vnames.",i,"~","M"));

          %end;

          %else %do;

            if missing(scan("&vlabels.",i,"~","M")) then

             rc=fput(fid,scan("&vnames.",i,"~"));

            else rc=fput(fid,scan("&vlabels.",i,"~","M"));

          %end;

        %end;

      end;

      %if %upcase(&usenames.) ne N %then %do;

        rc=fwrite(fid);;

      %end;

      do until (lastone);

        set &libnm..&filenm. &dsoptions. end=lastone;

        %include code2inc;

      end;

      rc=fclose(fid);

      rc=filename('clippy');

      rc=filename('code2inc');

      stop;

      err:

      do;

        m = sysmsg();

        put m;

        rc=filename('code2inc');

        stop;

      end;

    run;

    %if %upcase(&type.) eq N or %upcase(&type.) eq M

     or %upcase(&type.) eq A %then %do;

      %if %length(&server_path.) gt 2 %then %do;

        data _null_;

          length script filevar $256;

          script = catx('\',pathname('WORK'),'PasteIt1.vbs');

          filevar = script;

          script="'"||'cscript "'||trim(script)||'"'||"'";

          call symput('script',script);

          file dummy1 filevar=filevar recfm=v lrecl=512;

          put 'Dim objShell';

          put 'Set objShell = CreateObject("WScript.Shell")';

          script=catt('objShell.Run "notepad.exe'," &server_path.\clip.txt",'"');

          put script;

          put 'Do Until Success = True';

          put 'Success = objShell.AppActivate("Notepad")';

          put 'Wscript.Sleep 1000';

          put 'Loop';

          put %str('objShell.SendKeys "%E"');

          put 'Do Until Success = True';

          put 'Success = objShell.AppActivate("Notepad")';

          put 'Wscript.Sleep 1000';

          put 'Loop';

          put 'objShell.SendKeys "A"';

          put 'Do Until Success = True';

          put 'Success = objShell.AppActivate("Notepad")';

          put 'Wscript.Sleep 1000';

          put 'Loop';

          put %str('objShell.SendKeys "%E"');

          put 'Do Until Success = True';

          put 'Success = objShell.AppActivate("Notepad")';

          put 'Wscript.Sleep 1000';

          put 'Loop';

          put 'objShell.SendKeys "C"';

          put 'Do Until Success = True';

          put 'Success = objShell.AppActivate("Notepad")';

          put 'Wscript.Sleep 1000';

          put 'Loop';

          put %str('objShell.SendKeys "%F"');

          put 'Do Until Success = True';

          put 'Success = objShell.AppActivate("Notepad")';

          put 'Wscript.Sleep 1000';

          put 'Loop';

          put 'objShell.SendKeys "X"';

          put 'Do Until Success = True';

          put 'Success = objShell.AppActivate("Notepad")';

          put 'Wscript.Sleep 1000';

          put 'Loop';

          put 'objShell.SendKeys "{TAB}"';

          put 'WScript.Sleep 500';

          put 'objShell.SendKeys "{ENTER}"';

          put 'WScript.Quit';

        run;

        data _null_;

          call system(&script.);

        run;

      %end;

      data _null_;

        length script filevar $256;

        script = catx('\',pathname('WORK'),'PasteIt2.vbs');

        filevar = script;

        script="'"||'cscript "'||trim(script)||'"'||"'";

        call symput('script',script);

        file dummy1 filevar=filevar recfm=v lrecl=512;

   

        put 'Dim objExcel';

        put 'Dim Newbook';

        %if %length(&template.) gt 1 %then %do;

          %if %upcase(&type.) eq A %then put 'Dim OldBook';;

          put 'Set objExcel = CreateObject("Excel.Application")';

          put 'objExcel.Visible = True';

          %if %upcase(&type.) eq N %then %do;

            script=catt('Set Newbook = objExcel.Workbooks.Add("',

             "&template.",'")');

            put script;

            script=catt('objExcel.Sheets("',"&TemplateSheet.",

             '").Select');

            put script;

            script=catt('objExcel.Sheets("',"&TemplateSheet.",

             '").Name = "',"&sheet.",'"');

            put script;

            put 'objExcel.Visible = True';

            script=catt('objExcel.Sheets("',"&sheet.",

             '").Range("',"&range.",'").Activate');

            put script;

            script=catt('objExcel.Sheets("',"&sheet.",'").Paste');

            put script;

            script=catt('objExcel.Sheets("',"&sheet.",

             '").Range("A1").Select');

            put script;

            put 'objExcel.DisplayAlerts = False';

            script=catt('NewBook.SaveAs("',"&outfile.",'")');

            put script;

          %end;

          %else %do;

            script=catt('strFile="',"&outfile.",'"');

            put script;

            script=catt('Set OldBook=objExcel.Workbooks.Open("',

             "&outfile.",'")');

            put script;

            script=catt('Set Newbook = objExcel.Workbooks.Add("',

             "&template.",'")');

            put script;

            script=catt('objExcel.Sheets("',"&TemplateSheet.",

             '").Select');

            put script;

            script=catt('objExcel.Sheets("',"&TemplateSheet",

             '").Name ="',"&sheet.",'"');

            put script;

            put 'objExcel.Visible = True';

            script=catt('objExcel.Sheets("',"&sheet.",

             '").Range("',"&range.",'").Activate');

            put script;

            script=catt('objExcel.Sheets("',"&sheet.",

             '").Paste');

            put script;

            script=catt('objExcel.Sheets("',"&sheet.",

             '").Range("A1").Select');

            put script;

            script=catt('objExcel.Sheets("',"&sheet.",

             '").Move ,OldBook.Sheets( OldBook.Sheets.Count )');

            put script;

            put 'objExcel.DisplayAlerts = False';

            script=catt('OldBook.SaveAs("',

             "&outfile.",'")');

            put script;

          %end;

        %end;

        %else %do;

          %if %upcase(&type.) eq N or %upcase(&type.) eq A %then %do;

            %if %upcase(&type.) eq N %then put 'Dim NewSheet';;

            put 'Dim inSheetCount';

            %if %upcase(&type.) eq A %then put 'Dim strFile';;

          %end;

          put 'Set objExcel = CreateObject("Excel.Application")';

          %if %upcase(&type.) eq N %then %do;

            put 'Set Newbook = objExcel.Workbooks.Add()';

            put 'objExcel.Visible = True';

            put 'inSheetCount = Newbook.Application.Worksheets.Count';

            script=catt('set NewSheet = Newbook.Sheets.Add',

             '( ,objExcel.WorkSheets(inSheetCount))');

            put script;

            put 'objExcel.DisplayAlerts = False';

            put 'i = inSheetCount';

            put 'Do Until i = 0';

            put ' Newbook.Worksheets(i).Delete';

            put ' i = i - 1';

            put ' Loop';

            script=catt('Newbook.Sheets(1).Name="',

             "&sheet.",'"');

            put script;

            script=catt('Newbook.Sheets("',"&sheet.",'").Select');

            put script;

            script=catt('Newbook.Sheets("',"&sheet.",

             '").Range("',"&range.",'").Activate');

            put script;

            script=catt('Newbook.Sheets("',"&sheet.",'").Paste');

            put script;

            script=catt('NewSheet.SaveAs("',"&outfile.",'")');

            put script;

          %end;

          %else %if %upcase(&type.) eq A %then %do;

            script=catt('strFile="',"&outfile.",'"');

            put script;

            put 'objExcel.Visible = True';

            put 'objExcel.Workbooks.Open strFile';

            put 'inSheetCount = objExcel.Application.Worksheets.Count';

            script=catt('set NewBook = objExcel.Sheets.Add( ,objExcel.',

             'WorkSheets(inSheetCount))');

            put script;

            script=catt('objExcel.Sheets(inSheetCount + 1).Name="',

             "&sheet.",'"');

            put script;

            script=catt('objExcel.Sheets("',"&sheet.",

             '").Select');

            put script;

            put 'objExcel.Visible = True';

            script=catt('objExcel.Sheets("',"&sheet.",'").Range("',

             "&range.",'").Activate');

            put script;

            script=catt('objExcel.Sheets("',"&sheet.",'").Paste');

            put script;

            put 'objExcel.DisplayAlerts = False';

            script=catt('Newbook.SaveAs("',"&outfile.",'")');

            put script;

          %end;

          %else %do;

            script=catt('Set Newbook = objExcel.Workbooks.Open("',

             "&outfile.",'")');

            put script;

            script=catt('Newbook.Sheets("',"&sheet.",'").Select');

            put script;

            script=catt('Newbook.Sheets("',"&sheet.",

             '").Range("',"&range.",'").Activate');

            put script;

            script=catt('Newbook.Sheets("',"&sheet.",'").Paste');

            put script;

            put 'objExcel.DisplayAlerts = False';

            script=catt('Newbook.SaveAs("',"&outfile.",'")');

            put script;

          %end;

        %end;

        put 'objExcel.Workbooks.Close';

        put 'objExcel.DisplayAlerts = True';

        put 'objExcel.Quit';

      run;

      data _null_;

        call system(&script.);

      run;

    %end;

  %end;

%mend exportxl;

/*Example*/

proc format;

  value $gender

  'F'='Female'

  'M'='Male'

  ;

  value agegroups

  10-12='Younger'

  13-15='Average'

  16-20='Older'

  ;

run;

data test;

  length name $8;

  length sex $6;

  set sashelp.class;

  format sex $gender.;

  format age agegroups.;

  label sex='Gender';

  label age='Age Group';

run;

%exportxl( data=test,

outfile=c:\temp\server_formatted.xlsx,

type=N,

usenames=L,

useformats=Y,

server_path=c:\temp)


Accepted Solutions
Solution
‎10-21-2014 11:41 AM
New Contributor
Posts: 4

Re: Will the following run in a Window's server or EG environment?

Hi Art,

I ran the code on a windows 2008 R2 server in base SAS 9.2 and it ran in 13.2 seconds.

Regards,

Mark

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,295

Re: Will the following run in a Window's server or EG environment?

Still looking for a volunteer(s). Can someone talk a minute to test this?

Art

Respected Advisor
Posts: 3,063

Re: Will the following run in a Window's server or EG environment?

Art- I tried running it on our SAS server but the NOXCMD option prevented it working.... 

Esteemed Advisor
Posts: 7,295

Re: Will the following run in a Window's server or EG environment?

Thanks for trying but, most definitely, the example can't run in an environment that doesn't allow one to run system commands.

Regular Learner
Posts: 1

Re: Will the following run in a Window's server or EG environment?

I just tested on SAS EG 5.1. The SAS server is local. Version 9.3. MS Office 2010.

It works with no errors, no warnings. clip.txt and result xlsx files are created.

Esteemed Advisor
Posts: 7,295

Re: Will the following run in a Window's server or EG environment?

Outstanding! Very much appreciated! Now I only need someone to try it on a server that isn't local.

Valued Guide
Posts: 3,206

Re: Will the following run in a Window's server or EG environment?

Arthur, is rather easy to download the UE and use that one. It is free. May be you will become the 100.001 the user :<).

You will get a server version running on Unix (CentOS Linux a looke alike of redhat).

All testing of your macro can be done with that. 

It will not run (I tried) caused by other physical naming conditions and other constraints.

---->-- ja karman --<-----
Esteemed Advisor
Posts: 7,295

Re: Will the following run in a Window's server or EG environment?

: Unless I misunderstood your note, no, it wouldn't let me test the one aspect they I'd like to discover, namely whether the revised code will run in a Window's server environment.

Valued Guide
Posts: 3,206

Re: Will the following run in a Window's server or EG environment?

Yep your are right, my mistake.

I understood it for a more generic server approach. By that as Unix is getting that much attention it was that one I thought you were asking.

When it runs on a Unix server as well as on you Windows desktop, I am convinced there should be no problem with a Windows server.

---->-- ja karman --<-----
Esteemed Advisor
Posts: 7,295

Re: Will the following run in a Window's server or EG environment?

: Did I miss something you wrote? Were you saying that the code run correctly on UNIX?

Solution
‎10-21-2014 11:41 AM
New Contributor
Posts: 4

Re: Will the following run in a Window's server or EG environment?

Hi Art,

I ran the code on a windows 2008 R2 server in base SAS 9.2 and it ran in 13.2 seconds.

Regards,

Mark

Esteemed Advisor
Posts: 7,295

Re: Will the following run in a Window's server or EG environment?

The_TaskMan: Much appreciated! I wasn't concerned about how much time it would take as I knew the method wouldn't work as efficiently as the original code designed for a non-server environment. My only concern was whether it would work in a server environment.

Valued Guide
Posts: 3,206

Re: Will the following run in a Window's server or EG environment?

This time I think you have missed some things. I think most of the code of your macro could run on Unix.

When the method of saving to the clipbord would be an optional xml file that must be able to be imported.

That method of using a xml-file could be extended to using xml-files (multiple ranges). That could be adding more functionality then the alternate approach of an export.

The only other thing to take care is naming conventions of all those files.    (download / execute)

---->-- ja karman --<-----
Esteemed Advisor
Posts: 7,295

Re: Will the following run in a Window's server or EG environment?

: I agree with your xml recommendation, but am not sufficiently proficient with XML to incorporate it in the macro thus will have to leave that to someone else.

☑ This topic is SOLVED.

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

Discussion stats
  • 13 replies
  • 635 views
  • 6 likes
  • 5 in conversation