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

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)

1 ACCEPTED SOLUTION

Accepted Solutions
The_TaskMan
Fluorite | Level 6

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

13 REPLIES 13
art297
Opal | Level 21

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

Art

SASKiwi
PROC Star

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

art297
Opal | Level 21

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

MaksimK
Calcite | Level 5

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.

art297
Opal | Level 21

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

jakarman
Barite | Level 11

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

: 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.

jakarman
Barite | Level 11

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

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

The_TaskMan
Fluorite | Level 6

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

art297
Opal | Level 21

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.

jakarman
Barite | Level 11

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

: 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.

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
  • 13 replies
  • 1559 views
  • 6 likes
  • 5 in conversation