Creating a dynamic variable in sas using macros

[Saurabh_Rana]

I have 3 tables with format like:-

"Country_Code"_customer_list  

"Country_Code"_sales_data

"Country_Code"_payment_data

The country code in the prefex changes but the table name remains the same.For ex:-

IND_customer_list  

IND_sales_data

IND_payment_data

AUS_customer_list  

AUS_sales_data

AUS_payment_data

So basically I want to write a single sas program for each country where I have to just change the country code once and the table names get renamed accordingly.

How can I do this using macros?I have no experience in using macros.

[yabwon]

Hi,

Start with something like this:

%macro EXAMPLE(Country_Code);
%put 
&Country_Code._customer_list  
&Country_Code._sales_data
&Country_Code._payment_data
;
%mend EXAMPLE;

%EXAMPLE(IND)
%EXAMPLE(AUS)

and adjust according to your needs.

All the best

Bart

[Saurabh_Rana]

Suppose I am using a PROC SQL command then am I supposed to write it like this:-

PROC SQL SELECT
.
.
.
FROM &Country_Code._customer_list

[Saurabh_Rana]

I am getting error while using these tables in PROC SQL

[Kurt_Bremser]

Please post your log, using the </> button.

[Saurabh_Rana]

<PROC SQL;
84 CREATE TABLE WORK.Customer_list AS
85 SELECT t1.NO_ as CUSTOMER_NO_,
86 t1.NAME,
87 /* CUSTOMER_SINCE */
88 (INPUT(SUBSTR(LEFT(t1.CUSTOMER_SINCE),1,10),yymmdd10.)) FORMAT=NLDATE10. LABEL="NLDATE10." AS CUSTOMER_SINCE,
89 t1.Customer_Status,
90 t1.CUSTOMER_POSTING_GROUP
91 FROM MYCAS.&._01A_CUSTOMER_LIST t1
             -
            22
            200
ERROR 22-322: Expecting a name.
ERROR 200-322: The symbol is not recognized and will be ignored./>

[Kurt_Bremser]

---

@Saurabh_Rana wrote:

<PROC SQL;
84 CREATE TABLE WORK.Customer_list AS
85 SELECT t1.NO_ as CUSTOMER_NO_,
86 t1.NAME,
87 /* CUSTOMER_SINCE */
88 (INPUT(SUBSTR(LEFT(t1.CUSTOMER_SINCE),1,10),yymmdd10.)) FORMAT=NLDATE10. LABEL="NLDATE10." AS CUSTOMER_SINCE,
89 t1.Customer_Status,
90 t1.CUSTOMER_POSTING_GROUP
91 FROM MYCAS.&._01A_CUSTOMER_LIST t1
             -
            22
            200
ERROR 22-322: Expecting a name.
ERROR 200-322: The symbol is not recognized and will be ignored./>

---

What it says: it expects the name of a macro variable between the ampersand and the dot.

[Saurabh_Rana]

I am getting an error while I am using this:-
FROM MYCAS.&Country_Code._01A_CUSTOMER_LIST t1

What exactly is the variable name in this code:-

%macro EXAMPLE(Country_Code);
%put
&Country_Code._customer_list
&Country_Code._sales_data
&Country_Code._payment_data
;
%mend EXAMPLE;

%EXAMPLE(IND)

[yabwon]

Let'u have one step back. Assuming that you are writing your code only for `IND` value what would the query be?

Try to turn on MPRINT option: `options mprint;` and look into the log, what does it say?

All the best
Bart

[Kurt_Bremser]

Country_Code is the macro parameter; inside the macro it is used like any other macro variable.

[Kurt_Bremser]

You should also question if it makes sense to split your data like that. A COUNTRY_CODE column in global datasets and WHERE conditions might be easier to handle.

[Saurabh_Rana]

Yes that was my first question to the client, but for some reason they want to do it that way.
Anyway thanks for the suggestion

[Kurt_Bremser]

Then they might consider this:

Create a directory tree like this:

/sasdata
  /sasdata/AUS
  /sasdata/IND
  .....

Then you write all your codes as

proc something data=inlib.dataset;

where INLIB is a library reference you create once at the start of your code like

libname inlib '/sasdata/AUS';

and all follow-up code can be static, without needing any macro processing.

[ballardw]

---

@Saurabh_Rana wrote:
Yes that was my first question to the client, but for some reason they want to do it that way.
Anyway thanks for the suggestion

---

I may be  bit of  a maverick in some things but if what the client sees is what the client wants then what goes on behind the scenes shouldn't matter. Take the data, combine in a way that makes sense and create the output needed. A specified "process" that is questionable may be harder to maintain/update/extend later.

[Tom · Accepted solution]

Sounds like you need to first understand how to use macro variables to replace part of your code.

First write the code to work for one country using names like 

IND_customer_list  
IND_sales_data
IND_payment_data

in the code.

Next add this line to the top of the program to define a macro VARIABLE named country_code:

%let country_code=IND;

and change the places in the code to use the macro variable to generate the name.  Like this:

&country_code.customer_list

If that works then you might be able to just stop there.  To run the program for a different country just change the value assigned to COUNTRY_CODE macro variable at the top of the program.

But you might want or need to to make a macro.  For that you remove the %LET and wrap the whole program inside of a macro definition that uses that macro variable name as the name of the input parameter.  And finally you need to call the macro to get it to actually run, supplying the value for country.

Example:

%macro mymacro(country_code);
.... &country_code.customer_list ...
%mend mymacro;

%mymacro(IND);
%mymacro(AUS);