topic Re: input excel info into macro syntax in SAS Programming

input excel info into macro syntax

harrylui — Thu, 12 Mar 2020 07:52:52 GMT

good day,

below is my program

%macro sqlmerchant(A=,B=,C=,ab=);

PROC SQL;
CREATE TABLE &A AS
SELECT * ,"&ab" AS NAME ,"Y" as Cleaned_flag FROM Merch_clean_latest_raw_backup
WHERE Merchant_Name LIKE &b
AND "merchant_name_alias.mcc"N IN (SELECT MCC FROM Merchant_list_v2 WHERE 'Merchant Category'n in &C )
;

%MEND;

%sqlmerchant(A=MCDONALD,B="%MCDONALD%",C=("Miscellaneous Stores"),ab=Mcdonald);

%sqlmerchant(A=WATSONSWINE,B="%WATSONSWINE%",C=("Miscellaneous Stores"),ab=Watsons Wine);

%sqlmerchant(A=WATSONS,B="%WATSONS%",C=("Miscellaneous Stores"),ab=Watsons);

here is my macro code.I have to search a list of merchant in a big data base.

and the list of merchant name and merchant code are stored in two excel.

can i set up a program which can let the macro read from the excel automatically . so that i can avoid to input all my name list into macro.

thanks in advance,

Harry

Re: input excel info into macro syntax

Kurt_Bremser — Thu, 12 Mar 2020 08:01:40 GMT

Combine your Excels into one dataset, from which you can then use call execute() to call your macro.

Re: input excel info into macro syntax

Reeza — Thu, 12 Mar 2020 15:20:10 GMT

Sure, import your Excel workbook and then you have that in a data set. You can pass the parameters to a macro call using CALL EXECUTE.

The CALL EXECUTE documentation has an example at the bottom that illustrates the idea.