SAS export subset of column to worksheet with a column name










0














Given a SAS dataset with columns named n1,n2,..nN.



Is there a simple way to export common set of columns and unique subset of columns to a workbook, where each column is exported to the worksheet with the same name as the last column name?



Example:



For the SAS dataset above, the columns:




  • n1, n2, n5 -> Worksheet n5


  • n1, n2, n9 -> Worksheet n9


  • n1, n2, n13 -> Worksheets n13

are exported to a Excel workbook, with worksheets named as above.



Appreciate any suggestions.










share|improve this question























  • If those last columns are all the same type, character or numeric, you can transpose the data and have this be entirely data driven using ODS EXCEL and BY GROUPS. Similar to this: support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data
    – Reeza
    Nov 12 '18 at 5:48
















0














Given a SAS dataset with columns named n1,n2,..nN.



Is there a simple way to export common set of columns and unique subset of columns to a workbook, where each column is exported to the worksheet with the same name as the last column name?



Example:



For the SAS dataset above, the columns:




  • n1, n2, n5 -> Worksheet n5


  • n1, n2, n9 -> Worksheet n9


  • n1, n2, n13 -> Worksheets n13

are exported to a Excel workbook, with worksheets named as above.



Appreciate any suggestions.










share|improve this question























  • If those last columns are all the same type, character or numeric, you can transpose the data and have this be entirely data driven using ODS EXCEL and BY GROUPS. Similar to this: support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data
    – Reeza
    Nov 12 '18 at 5:48














0












0








0







Given a SAS dataset with columns named n1,n2,..nN.



Is there a simple way to export common set of columns and unique subset of columns to a workbook, where each column is exported to the worksheet with the same name as the last column name?



Example:



For the SAS dataset above, the columns:




  • n1, n2, n5 -> Worksheet n5


  • n1, n2, n9 -> Worksheet n9


  • n1, n2, n13 -> Worksheets n13

are exported to a Excel workbook, with worksheets named as above.



Appreciate any suggestions.










share|improve this question















Given a SAS dataset with columns named n1,n2,..nN.



Is there a simple way to export common set of columns and unique subset of columns to a workbook, where each column is exported to the worksheet with the same name as the last column name?



Example:



For the SAS dataset above, the columns:




  • n1, n2, n5 -> Worksheet n5


  • n1, n2, n9 -> Worksheet n9


  • n1, n2, n13 -> Worksheets n13

are exported to a Excel workbook, with worksheets named as above.



Appreciate any suggestions.







excel sas export






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 11 '18 at 22:18

























asked Nov 11 '18 at 22:09









Hedgehog

3,38222632




3,38222632











  • If those last columns are all the same type, character or numeric, you can transpose the data and have this be entirely data driven using ODS EXCEL and BY GROUPS. Similar to this: support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data
    – Reeza
    Nov 12 '18 at 5:48

















  • If those last columns are all the same type, character or numeric, you can transpose the data and have this be entirely data driven using ODS EXCEL and BY GROUPS. Similar to this: support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data
    – Reeza
    Nov 12 '18 at 5:48
















If those last columns are all the same type, character or numeric, you can transpose the data and have this be entirely data driven using ODS EXCEL and BY GROUPS. Similar to this: support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data
– Reeza
Nov 12 '18 at 5:48





If those last columns are all the same type, character or numeric, you can transpose the data and have this be entirely data driven using ODS EXCEL and BY GROUPS. Similar to this: support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#data
– Reeza
Nov 12 '18 at 5:48













2 Answers
2






active

oldest

votes


















1














Use the SHEET= statement in a Proc EXPORT step.



For example:



filename myxl 'c:tempsandbox.xlsx';

proc export replace file=myxl dbms=excel
data=sashelp.class (keep=name)
;
sheet='Name';
run;

proc export replace file=myxl dbms=excel
data=sashelp.class (keep=name age weight)
;
sheet='Weight';
run;


A macro can be coded to generate repetitive parts



%macro excel_push (file=, data=, always=, each=);
%local i n var;
%let n = %sysfunc(countw(&each));
%do i = 1 %to &n;
%let var = %scan(&each,&i);

proc export replace file=&file dbms=excel
data=&data(keep=&always &var)
;
sheet="&var";
run;

%end;

%mend;

options mprint;

filename myxl2 'c:tempsandbox2.xlsx';

%excel_push (
file=myxl2,
data=sashelp.class,
always=name age sex,
each=height weight
)


If you open the Excel output, leave it open and rerun the code, you will get an error, albeit slightly obscure:



ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: The Microsoft Access database
engine could not find the object ********. Make sure the object exists and that you spell
its name and the path name correctly. If ******** is not a local object, check your
network connection or contact the server administrator..





share|improve this answer






















  • Very nice. The macro I came up with was more clunky. Much appreciated.
    – Hedgehog
    Nov 12 '18 at 2:54


















0














I guess, What I understood from your question is how to have sheet name with last variable of SAS dataset. One way to do this is to use dictionary.columns and find which column position (varnum in dictionary.columns)is max in a dataset which will give the last variable and you can make a macro variable out of this and use this for sheet in proc export.



/* pick up the last variable*/
proc sql ;
select name into :mysheet TRIMMED from dictionary.columns
where libname = "SASHELP"
and memname = "CLASS"
and varnum = (select max(varnum) from dictionary.columns
where libname = "SASHELP"
and memname = "CLASS");

/* use the macrovariable in your sheet statement*/

PROC EXPORT DATA= Sashelp.Class /*Sheet 1*/
outfile= "/folders/myfolders/class.xlsx "
dbms=xlsx replace;
sheet="&mysheet";
run;





share|improve this answer






















    Your Answer






    StackExchange.ifUsing("editor", function ()
    StackExchange.using("externalEditor", function ()
    StackExchange.using("snippets", function ()
    StackExchange.snippets.init();
    );
    );
    , "code-snippets");

    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "1"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader:
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    ,
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













    draft saved

    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253745%2fsas-export-subset-of-column-to-worksheet-with-a-column-name%23new-answer', 'question_page');

    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    Use the SHEET= statement in a Proc EXPORT step.



    For example:



    filename myxl 'c:tempsandbox.xlsx';

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name)
    ;
    sheet='Name';
    run;

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name age weight)
    ;
    sheet='Weight';
    run;


    A macro can be coded to generate repetitive parts



    %macro excel_push (file=, data=, always=, each=);
    %local i n var;
    %let n = %sysfunc(countw(&each));
    %do i = 1 %to &n;
    %let var = %scan(&each,&i);

    proc export replace file=&file dbms=excel
    data=&data(keep=&always &var)
    ;
    sheet="&var";
    run;

    %end;

    %mend;

    options mprint;

    filename myxl2 'c:tempsandbox2.xlsx';

    %excel_push (
    file=myxl2,
    data=sashelp.class,
    always=name age sex,
    each=height weight
    )


    If you open the Excel output, leave it open and rerun the code, you will get an error, albeit slightly obscure:



    ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: The Microsoft Access database
    engine could not find the object ********. Make sure the object exists and that you spell
    its name and the path name correctly. If ******** is not a local object, check your
    network connection or contact the server administrator..





    share|improve this answer






















    • Very nice. The macro I came up with was more clunky. Much appreciated.
      – Hedgehog
      Nov 12 '18 at 2:54















    1














    Use the SHEET= statement in a Proc EXPORT step.



    For example:



    filename myxl 'c:tempsandbox.xlsx';

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name)
    ;
    sheet='Name';
    run;

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name age weight)
    ;
    sheet='Weight';
    run;


    A macro can be coded to generate repetitive parts



    %macro excel_push (file=, data=, always=, each=);
    %local i n var;
    %let n = %sysfunc(countw(&each));
    %do i = 1 %to &n;
    %let var = %scan(&each,&i);

    proc export replace file=&file dbms=excel
    data=&data(keep=&always &var)
    ;
    sheet="&var";
    run;

    %end;

    %mend;

    options mprint;

    filename myxl2 'c:tempsandbox2.xlsx';

    %excel_push (
    file=myxl2,
    data=sashelp.class,
    always=name age sex,
    each=height weight
    )


    If you open the Excel output, leave it open and rerun the code, you will get an error, albeit slightly obscure:



    ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: The Microsoft Access database
    engine could not find the object ********. Make sure the object exists and that you spell
    its name and the path name correctly. If ******** is not a local object, check your
    network connection or contact the server administrator..





    share|improve this answer






















    • Very nice. The macro I came up with was more clunky. Much appreciated.
      – Hedgehog
      Nov 12 '18 at 2:54













    1












    1








    1






    Use the SHEET= statement in a Proc EXPORT step.



    For example:



    filename myxl 'c:tempsandbox.xlsx';

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name)
    ;
    sheet='Name';
    run;

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name age weight)
    ;
    sheet='Weight';
    run;


    A macro can be coded to generate repetitive parts



    %macro excel_push (file=, data=, always=, each=);
    %local i n var;
    %let n = %sysfunc(countw(&each));
    %do i = 1 %to &n;
    %let var = %scan(&each,&i);

    proc export replace file=&file dbms=excel
    data=&data(keep=&always &var)
    ;
    sheet="&var";
    run;

    %end;

    %mend;

    options mprint;

    filename myxl2 'c:tempsandbox2.xlsx';

    %excel_push (
    file=myxl2,
    data=sashelp.class,
    always=name age sex,
    each=height weight
    )


    If you open the Excel output, leave it open and rerun the code, you will get an error, albeit slightly obscure:



    ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: The Microsoft Access database
    engine could not find the object ********. Make sure the object exists and that you spell
    its name and the path name correctly. If ******** is not a local object, check your
    network connection or contact the server administrator..





    share|improve this answer














    Use the SHEET= statement in a Proc EXPORT step.



    For example:



    filename myxl 'c:tempsandbox.xlsx';

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name)
    ;
    sheet='Name';
    run;

    proc export replace file=myxl dbms=excel
    data=sashelp.class (keep=name age weight)
    ;
    sheet='Weight';
    run;


    A macro can be coded to generate repetitive parts



    %macro excel_push (file=, data=, always=, each=);
    %local i n var;
    %let n = %sysfunc(countw(&each));
    %do i = 1 %to &n;
    %let var = %scan(&each,&i);

    proc export replace file=&file dbms=excel
    data=&data(keep=&always &var)
    ;
    sheet="&var";
    run;

    %end;

    %mend;

    options mprint;

    filename myxl2 'c:tempsandbox2.xlsx';

    %excel_push (
    file=myxl2,
    data=sashelp.class,
    always=name age sex,
    each=height weight
    )


    If you open the Excel output, leave it open and rerun the code, you will get an error, albeit slightly obscure:



    ERROR: Error attempting to CREATE a DBMS table. ERROR: Execute: The Microsoft Access database
    engine could not find the object ********. Make sure the object exists and that you spell
    its name and the path name correctly. If ******** is not a local object, check your
    network connection or contact the server administrator..






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 11 '18 at 23:28

























    answered Nov 11 '18 at 23:13









    Richard

    8,17421227




    8,17421227











    • Very nice. The macro I came up with was more clunky. Much appreciated.
      – Hedgehog
      Nov 12 '18 at 2:54
















    • Very nice. The macro I came up with was more clunky. Much appreciated.
      – Hedgehog
      Nov 12 '18 at 2:54















    Very nice. The macro I came up with was more clunky. Much appreciated.
    – Hedgehog
    Nov 12 '18 at 2:54




    Very nice. The macro I came up with was more clunky. Much appreciated.
    – Hedgehog
    Nov 12 '18 at 2:54













    0














    I guess, What I understood from your question is how to have sheet name with last variable of SAS dataset. One way to do this is to use dictionary.columns and find which column position (varnum in dictionary.columns)is max in a dataset which will give the last variable and you can make a macro variable out of this and use this for sheet in proc export.



    /* pick up the last variable*/
    proc sql ;
    select name into :mysheet TRIMMED from dictionary.columns
    where libname = "SASHELP"
    and memname = "CLASS"
    and varnum = (select max(varnum) from dictionary.columns
    where libname = "SASHELP"
    and memname = "CLASS");

    /* use the macrovariable in your sheet statement*/

    PROC EXPORT DATA= Sashelp.Class /*Sheet 1*/
    outfile= "/folders/myfolders/class.xlsx "
    dbms=xlsx replace;
    sheet="&mysheet";
    run;





    share|improve this answer



























      0














      I guess, What I understood from your question is how to have sheet name with last variable of SAS dataset. One way to do this is to use dictionary.columns and find which column position (varnum in dictionary.columns)is max in a dataset which will give the last variable and you can make a macro variable out of this and use this for sheet in proc export.



      /* pick up the last variable*/
      proc sql ;
      select name into :mysheet TRIMMED from dictionary.columns
      where libname = "SASHELP"
      and memname = "CLASS"
      and varnum = (select max(varnum) from dictionary.columns
      where libname = "SASHELP"
      and memname = "CLASS");

      /* use the macrovariable in your sheet statement*/

      PROC EXPORT DATA= Sashelp.Class /*Sheet 1*/
      outfile= "/folders/myfolders/class.xlsx "
      dbms=xlsx replace;
      sheet="&mysheet";
      run;





      share|improve this answer

























        0












        0








        0






        I guess, What I understood from your question is how to have sheet name with last variable of SAS dataset. One way to do this is to use dictionary.columns and find which column position (varnum in dictionary.columns)is max in a dataset which will give the last variable and you can make a macro variable out of this and use this for sheet in proc export.



        /* pick up the last variable*/
        proc sql ;
        select name into :mysheet TRIMMED from dictionary.columns
        where libname = "SASHELP"
        and memname = "CLASS"
        and varnum = (select max(varnum) from dictionary.columns
        where libname = "SASHELP"
        and memname = "CLASS");

        /* use the macrovariable in your sheet statement*/

        PROC EXPORT DATA= Sashelp.Class /*Sheet 1*/
        outfile= "/folders/myfolders/class.xlsx "
        dbms=xlsx replace;
        sheet="&mysheet";
        run;





        share|improve this answer














        I guess, What I understood from your question is how to have sheet name with last variable of SAS dataset. One way to do this is to use dictionary.columns and find which column position (varnum in dictionary.columns)is max in a dataset which will give the last variable and you can make a macro variable out of this and use this for sheet in proc export.



        /* pick up the last variable*/
        proc sql ;
        select name into :mysheet TRIMMED from dictionary.columns
        where libname = "SASHELP"
        and memname = "CLASS"
        and varnum = (select max(varnum) from dictionary.columns
        where libname = "SASHELP"
        and memname = "CLASS");

        /* use the macrovariable in your sheet statement*/

        PROC EXPORT DATA= Sashelp.Class /*Sheet 1*/
        outfile= "/folders/myfolders/class.xlsx "
        dbms=xlsx replace;
        sheet="&mysheet";
        run;






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 12 '18 at 3:05

























        answered Nov 12 '18 at 2:56









        Kiran

        2,5323819




        2,5323819



























            draft saved

            draft discarded
















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid


            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.

            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53253745%2fsas-export-subset-of-column-to-worksheet-with-a-column-name%23new-answer', 'question_page');

            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Use pre created SQLite database for Android project in kotlin

            Darth Vader #20

            Ondo