topic Re: Creating a dynamic variable in sas using macros in SAS Programming

Creating a dynamic variable in sas using macros

Saurabh_Rana — Wed, 15 Apr 2020 05:48:12 GMT

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.

Re: Creating a dynamic variable in sas using macros

yabwon — Wed, 15 Apr 2020 05:54:39 GMT

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

Re: Creating a dynamic variable in sas using macros

Kurt_Bremser — Wed, 15 Apr 2020 06:06:53 GMT

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.

Re: Creating a dynamic variable in sas using macros

Saurabh_Rana — Wed, 15 Apr 2020 06:50:27 GMT

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

Re: Creating a dynamic variable in sas using macros

Saurabh_Rana — Wed, 15 Apr 2020 07:05:15 GMT

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

Re: Creating a dynamic variable in sas using macros

Kurt_Bremser — Wed, 15 Apr 2020 07:11:54 GMT

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.

Re: Creating a dynamic variable in sas using macros

Saurabh_Rana — Wed, 15 Apr 2020 07:39:56 GMT

I am getting error while using these tables in PROC SQL

Re: Creating a dynamic variable in sas using macros

Kurt_Bremser — Wed, 15 Apr 2020 07:41:42 GMT

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

Re: Creating a dynamic variable in sas using macros

Saurabh_Rana — Wed, 15 Apr 2020 07:49:54 GMT

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

Re: Creating a dynamic variable in sas using macros

Kurt_Bremser — Wed, 15 Apr 2020 07:54:55 GMT

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

Re: Creating a dynamic variable in sas using macros

Saurabh_Rana — Wed, 15 Apr 2020 12:24:11 GMT

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)

Re: Creating a dynamic variable in sas using macros

yabwon — Wed, 15 Apr 2020 12:34:48 GMT

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

Re: Creating a dynamic variable in sas using macros

Kurt_Bremser — Wed, 15 Apr 2020 13:06:12 GMT

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

Re: Creating a dynamic variable in sas using macros

Tom — Wed, 15 Apr 2020 13:57:01 GMT

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);

Re: Creating a dynamic variable in sas using macros

ballardw — Wed, 15 Apr 2020 14:13:09 GMT

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