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

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

SASGeek — Mon, 04 Jul 2016 14:26:23 GMT

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

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

rogerjdeangelis — Mon, 04 Jul 2016 17:43:39 GMT

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;

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

BrunoMueller — Mon, 04 Jul 2016 18:07:46 GMT

SAS Studio 3.5

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

Bruno

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

SASGeek — Mon, 04 Jul 2016 22:23:53 GMT

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

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

SASGeek — Mon, 04 Jul 2016 22:24:37 GMT

Thanks Bruno - SWEET. I'll give Roger's answer a try too - can't have too many tools in the SAS arsenal.

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

RobP — Fri, 01 Jun 2018 15:21:49 GMT

When I try Bruno's option from SAS Studio it saves it to the SAS server file system, which in my case is remote.

Was hoping it would trigger a download...

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

Audron — Mon, 27 Aug 2018 09:20:52 GMT

You can then browse to the file and right click and click save to trigger the download. It's an extra step, but it works. It would be nice if there would be a button that does this automatically in the output.

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

mvniekerk — Wed, 13 Nov 2019 15:53:46 GMT

I exported a very simple dataset to a .csv file. I want to use this data in excel to make a simple scatter plot graph. But for some reason when the data comes from SAS, excel does not want to draw a graph? Can anybody help?