Write and run SAS programs in your web browser

Exporting data sets to Excel from SAS Studio 3.5 like in PC SAS

Reply
Occasional Contributor
Posts: 5

Exporting data sets to Excel from SAS Studio 3.5 like in PC SAS

I have 30+ years of SAS programming experience (hello TSO/MVS!!) but am a total neophyte when it comes to SAS  Studio (but I like it). in the SAS PC version, when listing the data-sets, you can right click on a data-set and there is an option to immediately export to Excel. Is there such an option in Studio? If so, would someone please kindly point me to where it can be found?

 

I don't need to write code, I just want a quick export.

 

Thanks

Valued Guide
Posts: 505

Re: Exporting data sets to Excel from SAS Studio 3.5 like in PC SAS

I know you like SAS studio and far be it for me to suggest the old boring text editor, but I can highlight a dataset in the old text editor, hit a function key or type xlsh on the command line an excel will popup with the dataset. I can then save it anywhere. You could type xlsl on the command line to open the last dataset created in excel. Obviously you can easily tailor the commnd macro to do other things.

 

Here is the macro

 

I have attached a zip file that will demonstrates some function key settings and some command macros that are helpful.

 

I do have some additional command line capabilitues like 'EVLH' which will evaluate any highlighted formula line 2*3+6.

 

You need the old text editor because key SAS scripting capabilities have been removed in all subsequent editors, ir EE, EG, and SAS Studio (most notable the 'save' command.

 

%macro xlsh /cmd ;
   store;note;notesubmit '%xlsha;';
   run;
%mend xlsh;

%macro xlsha/cmd;

    filename clp clipbrd ;
    data _null_;
     infile clp;
     input;
     put _infile_;
     call symputx('argx',_infile_);
    run;

    %let __tmp=%sysfunc(pathname(work))\myxls.xlsx;

    data _null_;
        fname="tempfile";
        rc=filename(fname, "&__tmp");
        put rc=;
        if rc = 0 and fexist(fname) then
       rc=fdelete(fname);
    rc=filename(fname);
    run;

    libname __xls excel "&__tmp";
    data __xls.%scan(__&argx,1,%str(.));
        set &argx.;
    run;quit;
    libname __xls clear;

    data _null_;z=sleep(1);run;quit;

    options noxwait noxsync;
    /* Open Excel */
    x "'C:\Program Files\Microsoft Office\OFFICE14\excel.exe' &__tmp";
    run;quit;

%mend xlsha;
Attachment
Occasional Contributor
Posts: 5

Re: Exporting data sets to Excel from SAS Studio 3.5 like in PC SAS

Thanks Roger - I'll try it. Even though I frankly prefer the text editor (DOS girl here!!), I need to brush up on my macro code so I'll give it a try.

 

Appreciate the relly

SAS Super FREQ
Posts: 620

Re: Exporting data sets to Excel from SAS Studio 3.5 like in PC SAS

Hi

 

SAS Studio 3.5

In the Libraries Drawer use the RightMouseButton and you get a menu item to export the data, see also below

Capture.PNG

 

Bruno

Occasional Contributor
Posts: 5

Re: Exporting data sets to Excel from SAS Studio 3.5 like in PC SAS

Thanks Bruno - SWEET. I'll give Roger's answer a try too - can't have too many tools in the SAS arsenal.
Post a Question
Discussion Stats
  • 4 replies
  • 594 views
  • 5 likes
  • 3 in conversation