10-29-2015 01:22 AM - edited 10-29-2015 01:24 AM
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..
10-29-2015 05:30 AM
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!
10-29-2015 07:39 AM
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?
10-29-2015 08:10 PM
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.
10-29-2015 08:38 PM
Also, you might want to take a look at this question to see if gives you any ideas about your own:
10-29-2015 09:19 PM
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;
proc print data = data1;
Let me know if I am wrong, after all I am a new learner.
10-30-2015 07:04 AM
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;
proc print data = &data_out (obs=10);
title "10 sample records based on importing &data_in";
%retrieve (data_in=path\filename1, data_out=data1)
%retrieve (data_in=path\filename2, data_out=data2)
%retrieve (data_in=path\filename3, data_out=data3)
10-31-2015 11:05 AM
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.
10-31-2015 12:47 PM
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:
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.