SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

MACRO

Reply
Occasional Contributor
Posts: 12

MACRO

Hello,

 

I need to import 3 excel file into SAS file. How I can do this using MACRO?

 

Thanks.

Regular Contributor
Posts: 161

Re: MACRO

[ Edited ]
Posted in reply to docsaurmi

Hello - Is this what you are looking for ? 

 

 

%macro import1(inname,outname);
proc import datafile=&inname
      out=&outname
      DBMS=xlsx
      ;
run;
proc print data=&outname;
%mend import1;
%import1("/folders/myfolders/data/tourdates",work.in2);

 

Of course, everytime you call the macro %import1, there will be a new set of input & output files as I imagine can be set to 1 SAS dsn as you wanted..

 

Good Luck...!!!

Kannan Deivasigamani
Occasional Contributor
Posts: 12

Re: MACRO

Thanks. I am going to follow . Also I will let you know.

 

Super User
Super User
Posts: 7,977

Re: MACRO

Posted in reply to docsaurmi

Hi,

 

Try the search bar on the main page.  There are an average of 10 posts a week on this.  There are many ways and processes to import data, and also many reasons why not to use Excel.  In an ideal world there would be agreement between you and the vendor on what data is to be sent, and in what structured data transfer format - e.g. XML, CSV etc.  This would be drawn up in an agreement, then a test transfer, import programs written, and moved to production.  The nearer you can get to that process the better it will be for you.  Other wise you will have - data not matching over transfers, differing structures, problem data (specifically Excel).  

My recommendation is to use CSV at minimum (SaveAs from Excel if you have to), personally I would get the vendor to do this as the data in CSV may not be the same as in Excel (again, it hides things from you), problems are then their issue.  Then write a datastep import program where you specify how to import the data, don't leave it to a guessing procedure - proc import - to try to work out what you want to do.

 

Finally, if its an XLSX file and your on 9.4, then there is the libname excel statement which will create a library of all the sheets in the file, no need to import each - however it has all the flaws associated with Excel files so beware!

http://www2.sas.com/proceedings/sugi31/024-31.pdf

Super User
Posts: 5,513

Re: MACRO

Posted in reply to docsaurmi

In general, the first part of the answer always looks like this.  How would you do this without using a macro?  Once you know that, turning your code into a macro is the second step.  Do you know how to do this without a macro?

Occasional Contributor
Posts: 12

Re: MACRO

Posted in reply to Astounding

yes, I know how to import data from excel to sas data without using MACRO. 

 

Super User
Posts: 5,513

Re: MACRO

Posted in reply to docsaurmi

OK.  Show us the program you would use, and explain which parts of the program would have to change if you were to apply it three times to three separate incoming files.  That will guide us in choosing what parameters to build into the macro.

Super User
Posts: 5,513

Re: MACRO

Posted in reply to Astounding

Also, you might want to take a look at this question to see if gives you any ideas about your own:

 

https://communities.sas.com/t5/SAS-Data-Management/quot-ERROR-Variable-has-been-defined-as-both-char...

 

 

Occasional Contributor
Posts: 12

Re: MACRO

Posted in reply to Astounding

I don't know about MACRO, I am just trying to learn with the help of yours and myself. Still now, I just know how to import excel file like this-

 

proc import out = data1

datafile = "path\file name"

dbms = xls replace;

getnames = yes;

datarow = 2;

run;

proc print data = data1;

run;

 

Let me know if I am wrong, after all I am a new learner.

Super User
Posts: 5,513

Re: MACRO

Posted in reply to docsaurmi

OK, assuming that your non-macro program is working, this might be a suitable macro:

 

%macro retrieve (data_in=, data_out=);

   

proc import out = &data_out

datafile = "data_in"

dbms = xls replace;

getnames = yes;

datarow = 2;

run;

proc print data = &data_out (obs=10);

title "10 sample records based on importing &data_in";

run;

 

%mend retrieve;

 

%retrieve (data_in=path\filename1, data_out=data1)

%retrieve (data_in=path\filename2, data_out=data2)

%retrieve (data_in=path\filename3, data_out=data3)

Occasional Contributor
Posts: 12

Re: MACRO

Posted in reply to Astounding

why retrieve,  data_in=  and  data_out= are used here?

what they mean?

Regular Contributor
Posts: 161

Re: MACRO

[ Edited ]
Posted in reply to docsaurmi

They are the input parms to the macro listing; your i/p file and o/p ds name.

Kannan Deivasigamani
Super User
Posts: 5,513

Re: MACRO

Posted in reply to docsaurmi

You'll have to study up on macros a bit if you really want to understand them.  You can't learn them sufficiently by asking basic questions one at a time.  That being said ...

 

RETRIEVE is a name I selected for the macro.  You have to choose a name for the macro when you create it, but there is nothing special about that name.

 

DATA_IN is the path to the incoming spreadsheet.  Since you want a macro to refer to a number of different spreadsheets, the path to the incoming spreadsheet has to be permitted to change each time.

 

DATA_OUT is the name of the generated SAS data set.  Since you don't necessarily want to replace the same SAS data set each time you import a spreadsheet, that too has to be permitted to change each time.

 

Just like you have to select a name for the macro when you create it, you have to select names for any parameters that the macro will use (in this case, DATA_IN and DATA_OUT).  Again, there is nothing special about the names except that it is easier to follow the logic of a macro when you select meaningful names.  I could have selected THUMB and FINGER instead, but that would be less intuitive.

Super User
Super User
Posts: 7,977

Re: MACRO

Posted in reply to Astounding

Do note however, if you have SAS 9.4, and use the latest Excel format - XLSX, then you don't need to use macro or anything else.  There is a simple libname statement as I noted above:

http://www2.sas.com/proceedings/sugi31/024-31.pdf

 

What this does:

libname mylib excel "path_to_file\filename.xlsx";

 

Is to create library reference to the filename.xlsx file.  Within that libname each of the sheets will automatically have a dataset created for each tab in the spreadsheet.  No need for imports etc.

 

Do note however, as I mentioned, any form of excel import has inherent issues.

Ask a Question
Discussion stats
  • 13 replies
  • 681 views
  • 0 likes
  • 4 in conversation