+ Reply to Thread
Results 1 to 1 of 1

Thread: Importing excel sheets using macro statment

  1. #1
    Points: 35, Level: 1
    Level completed: 70%, Points required for next Level: 15

    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing excel sheets using macro statment




    I need to complete an four tasks as follows:
    There are 3 Excel files, DATAFILE1.XLS, DATAFILE2.XLS and DATAFILE3.XLS

    Copy these files into the folder: C:\SUM_2016\SUM2016_HW\HW6\

    1.Write a SAS macro to convert each one of these 3 Excel files into 3 SAS datasets

    2.Write a SAS macro to combine all records of DATAFILE3 and DATAFILE2.

    3.Write a SAS macro to combine DATAFILE1 and DATAFILE2 so that you have records only for those values of ID that are in DATAFILE1.

    4.Write a SAS macro to combine all three files so that you have records only for those values of ID that are in all 3 datasets.

    I submitted the following program, but my instructor said, "None of your macros have any arguments to it. You have used the actual datafile names without referring to them using a macro variable."

    Submitted program
    options nodate nonumber;
    libname hw6 '\\client\C$\SUM_2016\SUM2016_HW\HW6';

    /* Convert Excel files into datasets */;
    %macro imp(file=, sheet=, dsn=);
    proc IMPORT OUT=&dsn
    DATAFILE=&file DBMS=XLS REPLACE;
    GETNAMES=YES;
    run;
    %mend;
    /***/;
    %let import='\\client\C$\SUM_2016\SUM2016_HW\HW6\datafile1.xls';
    %imp(file=&import, dsn=dataset1);
    %let import='\\client\C$\SUM_2016\SUM2016_HW\HW6\datafile2.xls';
    %imp(file=&import, dsn=dataset2);
    %let import='\\client\C$\SUM_2016\SUM2016_HW\HW6\datafile3.xls';
    %imp(file=&import, dsn=dataset3);

    /* Combine all records of DATAFILE3 and DATAFILE2 */;
    %macro comb;
    data comb32;
    set dataset3 dataset2;
    run;
    %mend comb;
    %comb;

    /* Combine DfILE1 and DfILE2_for IDs from 1 */;
    %macro m12;
    data d1d2m_2;
    merge dataset1(in=a) dataset2;
    by id;
    if a;
    run;
    %mend m12;
    %m12;

    /* Combine all three files */;
    %macro m_all;
    data d1d2d3_1;
    merge dataset1 (in = a) dataset2 (in = b) dataset3(in = c);
    by id;
    if a and b and c;
    run;
    %mend m_all;
    %m_all

    proc print data = comb32;
    run;

    proc print data = d1d2m_2;
    run;

    proc print data = d1d2d3_1;
    run;

    Please help!!
    Attached Files
    Last edited by Gpepsicola; 11-13-2016 at 12:59 AM. Reason: To add information

+ Reply to Thread

           




Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts






Advertise on Talk Stats