Macros

[gsaba]

Hi, 

I'm new to SAS, so please don't judge me very harsh. I have one question about SAS Macro. 

I'm trying to make a macro which will import an Excel File. i have made that. with something like this: (I don't have here that code now, so i will try to remember it now)

%macro gsaba (a, b)

proc import out = &a

datafile= "C:desktop\excelfiles\25excelfile.xlsx&b";

dbms=xlsx replace;

run;

%mend 

%gsaba (a = datafile, b = 25excelfile.xlsx) 

so, this works! but now my question is how to make a filter for release. i want to make something where i can write "YES" and in this case, macro will work and load output, but when entering "NO" macro will not work. how can i do that? can somebody help? 

[Astounding]

Is this what you are asking?

%macro gsaba (a, b, filter)

%if &FILTER=YES %then %do;

proc import out = &a

datafile= "C:desktop\excelfiles\25excelfile.xlsx&b";

dbms=xlsx replace;

run;

%end;

%mend 

[gsaba]

Thanks, i think that's the answer. But as i understand, i need to mention in bottom what is FILTER, isn't it true? like this: 

%macro gsaba (a, b, filter)

%if &FILTER=YES %then %do;

proc import out = &a

datafile= "C:desktop\excelfiles\25excelfile.xlsx&b";

dbms=xlsx replace;

run;

%end;

%mend 

%gsaba (a = datafile, b = 25excelfile.xlsx, FILTER = "YES") 

as soon as i will have a possibility to check this code, i'll write if this works... 

many thanks in advance. 

[Astounding]

You do have to specify a value for FILTER.  However, don't use quotes around the YES.  Also, make these compatible:

%macro gsaba (a, b, filter);   would match with    %gsaba (datafile, 25excelfile.xlsx, YES)

Or (and the variation I prefer):

%macro gsaba (a=, b=, filter=);  would match with   %gsaba (a=datafile, b=25excelfile.xlsx, filter=YES)

[gsaba]

for some reason, when i Run this code, SAS returns ERROR message: 

ERROR: Expected %TO not found in %DO statement.

My code is like this: 

%macro gsaba (a, b, c);
%if &c = YES %then %do
proc import datafile= "C:\Users\gitonishvili\Desktop\&b"
dbms=xlsx
out= &a replace;
run;
%mend;

%gsaba (a = task5, b = SabadzeTask25.xlsx, c = YES);

Before %if statement, code was: 

%macro gsaba (a, b);
proc import datafile= "C:\Users\gitonishvili\Desktop\&b"
dbms=xlsx
out= &a replace;
run;
%mend;

%gsaba (a = task5, b = SabadzeTask25.xlsx); 

and it worked.... what can i do? 

[Astounding · Accepted solution]

You're missing a couple of pieces.  

%do  needs a semicolon:  %do;

And this is missing entirely to match with %do:  %end;

[gsaba]

Many thanks! 

It Works! Thank you really very much. 

now i have one question also if you could help me again: 

i have 5 tables with names: 

trans-1, trans-2, trans-3, trans-4, trans-5. 

in this macro, my task is to write a code, that can understand and append tables if i will mention that i want to append from trans-1 to trans-3. in this case, macro will append these three tables. is it possible? 

[Reeza]

Why use a macro?

The following will append all 5 of the data sets at once.

data want;
set trans1-trans5 indsname=source;
inputdata=source;
run;

Otherwise, add a proc append to your macro, and note that PROC APPEND does not require the base data set to exist originally. 

[gsaba]

Unfortunately, my task is to do all this stuff using Macro. 

for appending, now i have this: 

%MACRO gsabatask52 (1, 2, 3, 4, 5);

%DO i=&1 %TO &3 ;

%If &i.=&1 %Then %Do;
Data Append_Data;
Set A_&i.;
Run;
%End;

PROC PRINT DATA = APPEND_DT;
RUN;

%MEND;

%TEST(1 = work.products_2018_01, 2 = work.products_2018_02, 3 = work.products_2018_03, 4 = work.products_2018_04, 5 = work.products_2018_05);

SAS returns a bunch of Errors, i think that my code is really very dummy. could you tell me what's right to do here? these tables i have on SAS desktop. 

[Reeza]

If you're writing macros the process I've found that results in less bugs:

1. Make sure the code works without any macro coding. Start with working code.

2. Identify all dynamic portions

3. Convert one at a time using a macro variable testing each macro variable

4. Wrap in macro code

5. Add loop or call via call execute.

When someone says they're required to use a specific approach that says this is homework. 

---

@gsaba wrote:

Unfortunately, my task is to do all this stuff using Macro. 

for appending, now i have this: 

%MACRO gsabatask52 (1, 2, 3, 4, 5);

%DO i=&1 %TO &3 ;

%If &i.=&1 %Then %Do;
Data Append_Data;
Set A_&i.;
Run;
%End;

PROC PRINT DATA = APPEND_DT;
RUN;

%MEND;

%TEST(1 = work.products_2018_01, 2 = work.products_2018_02, 3 = work.products_2018_03, 4 = work.products_2018_04, 5 = work.products_2018_05);

SAS returns a bunch of Errors, i think that my code is really very dummy. could you tell me what's right to do here? these tables i have on SAS desktop. 

---

[gsaba]

no, it is not a homework. at work, my boss, said that i should work in SAS. they know that i'm not quite well in this. there is one guy, who gives me daily tasks for exercising in SAS and he told, that i could make this tasks with help of internet. he thinks i will learn it better this way. 

[Tom]

Macro parameter names must be valid SAS names.  You can't use 1,2,3, as parameter names.

If you want to make a macro that takes in a list of values then just use one parameter for the list. Pass in a space delimited set of values.  Or if the values can contain spaces then use some other character, like |, that does not appear in the data.

%macro process_list(list);
%local i item ;
%do i=1 %to %sysfunc(countw(&list,%str( )));
  %let item=%scan(&list,&i,%str( ));
 ....
%end;
%mend ;

[Astounding]

The first step is get a working non-macro program.  How would you combine the first 3 tables without macro language?

Even if your ultimate goal is to write a macro, you would still begin by writing a program with no macro language.  Remember, macro language does NOT process your data.  Macro language generates a program, and the program processes your data.  You need a clear picture of what the program should look like, in order to write a macro that will generate the proper program.

[Tom]

---

@Astounding wrote:

You do have to specify a value for FILTER.  However, don't use quotes around the YES.  Also, make these compatible:

%macro gsaba (a, b, filter);   would match with    %gsaba (datafile, 25excelfile.xlsx, YES)

Or (and the variation I prefer):

%macro gsaba (a=, b=, filter=);  would match with   %gsaba (a=datafile, b=25excelfile.xlsx, filter=YES)

---

You can call the macro with parameter names, even for positional parameter. 

You just can't try to pass values for named parameter by position.

So if you define your macro like this:

%macro gsaba (a, b, filter);   ... %mend;

 You could use any of these calls:

%gsaba (datafile, 25excelfile.xlsx, YES)
%gsaba (a=datafile, b=25excelfile.xlsx, filter=YES)
%gsaba (datafile, b=25excelfile.xlsx, filter=YES)
%gsaba (b=25excelfile.xlsx, filter=YES,a=datafile)

[RW9]

A couple of tips for you. 

- Only use macro if you really have too and it adds value, otherwise you will find your code messy and unstable as it is just a text find and replace system, its not to replace base SAS.  In your given example, the code is the proc import, which is a simple, well known bit of code, what value is there in wrapping that in some code only you understand thus making it harder for other programmers to work out (i.e. think about if the code was hidden in a catalog, what does gsaba(a,b) do or mean?  Without documentation this is called obfuscation and is a real problem in coding).

- Avoid using proc import, Excel is a really bad data format, proc import is a guessing procedure, if you put those two together then most of the time you run that code, you will end up with a dataset which doesn't match a previous run (Excel allows anything in cells, proc import does its best to guess what is best).  A proper import procedure of documented import source e.g. structure, and a fixed datastep import program will ensure that each run the same thing happens.