BookmarkSubscribeRSS Feed
docsaurmi
Calcite | Level 5

Hello,

 

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

 

Thanks.

13 REPLIES 13
kannand
Lapis Lazuli | Level 10

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
docsaurmi
Calcite | Level 5

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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?

docsaurmi
Calcite | Level 5

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

 

Astounding
PROC Star

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.

Astounding
PROC Star

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...

 

 

docsaurmi
Calcite | Level 5

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.

Astounding
PROC Star

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)

docsaurmi
Calcite | Level 5

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

what they mean?

kannand
Lapis Lazuli | Level 10

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

Kannan Deivasigamani
Astounding
PROC Star

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 1627 views
  • 0 likes
  • 4 in conversation