topic Re: Create table name from passed variable in SAS Enterprise Guide

Create table name from passed variable

deleted_user — Mon, 30 Aug 2010 12:37:13 GMT

Hi all,

i'm still very new with SAS EG and SAS Macro.
At first i thought it was gonna be no problem to learn it, since i've got some experience in programming and databases.
But surely i was totally wrong!
Hopefully can learn a lot from more seasoned users here

I created some code as below:

/*---BEGIN CODE--- */
%MACRO CRT_TBL_GRW(officeID);
%LET TABLE_NAME='WORK._' || %put(&officeID);
%PUT &TABLE_NAME;

PROC SQL OUTOBS=5;
CREATE TABLE &TABLE_NAME AS SELECT DISTINCT TOP5GROWTH_OF_OS_CA_INC.AccountId FORMAT=$20.,
TOP5GROWTH_OF_OS_CA_INC.AccountName FORMAT=$20.,
TOP5GROWTH_OF_OS_CA_INC.CustomerId FORMAT=$20.,
TOP5GROWTH_OF_OS_CA_INC.ProductCategory FORMAT=$50.,
TOP5GROWTH_OF_OS_CA_INC.FunctionalGroup FORMAT=$40.,
TOP5GROWTH_OF_OS_CA_INC.OfficeId FORMAT=11.,
TOP5GROWTH_OF_OS_CA_INC.CurrentBalance_IDR FORMAT=COMMA17.1,
TOP5GROWTH_OF_OS_CA_INC.Calculation1
FROM WORK.TOP5GROWTH_OF_OS_CA_INC AS TOP5GROWTH_OF_OS_CA_INC
WHERE TOP5GROWTH_OF_OS_CA_INC.ProductCategory = "GIRO" AND TOP5GROWTH_OF_OS_CA_INC.OfficeId = &officeID
GROUP BY TOP5GROWTH_OF_OS_CA_INC.OfficeId
ORDER BY TOP5GROWTH_OF_OS_CA_INC.OfficeId, TOP5GROWTH_OF_OS_CA_INC.Calculation1 DESCENDING;
QUIT;

%MEND;

%CRT_TBL_GRW(2384);
%CRT_TBL_GRW(2023);

/* ---END CODE---*/

I want to pass parameter through variable and create several tables, but i couldn't manage to run this code properly.

The log says:
NOTE: Line generated by the macro variable "TABLE_NAME".
44 'WORK._' ||
__
22
ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 22-322: Syntax error, expecting one of the following: (, AS, LIKE.

Please help, i need some enlightment

thank you

Best Regards,
M. Djojo

Re: Create table name from passed variable

Adrian_Mc — Mon, 30 Aug 2010 14:25:38 GMT

You shouldn't need to use the %put() function around the &officeID macro variable.
Try it without it. If there's still an issue you could use the %sysfunc(left(&officeId)) function to left align the office id.

Hope that helps...

Re: Create table name from passed variable

sbb — Mon, 30 Aug 2010 14:37:30 GMT

Constructing macro variables is not like a DATA step approach - no concatenation (vertical bars) and no quote-marks.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

using macro variables site:sas.com

SAS DOC reference:
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a002047074.htm

Re: Create table name from passed variable

Cynthia_sas — Mon, 30 Aug 2010 16:03:10 GMT

Hi:
As other folks have explained, macro programming and macro variable concatenation is NOT like DATA step programming or DATA step variable concatenation. This is a good introduction to the concepts of macro variable referencing and writing SAS Macro programs.
http://www2.sas.com/proceedings/sugi28/056-28.pdf

cynthia

Re: Create table name from passed variable

deleted_user — Tue, 31 Aug 2010 05:24:46 GMT

Hi all,

The macro runs perfectly now, many thanks for your feedbacks.
I will definitely take a while before getting familiar with SAS codes.

Thank you

Best Regards,
M. Djojo