Macro error o

[meckarthik]

Hi trying to execute macro for 1 to n using below code. getting an error on the macro name.

I tried to create a table in below proc SQL although the date appearing correctly &client_id_CUSTOMER_HEADER_&todaysDate

(&client_id_CUSTOMER_HEADER_2019-08-02) but &client_id is not populating in the newly created table, getting an error . Below are the code and detail error. any help, pls  

basically got a table with 3 rows 

table test

id	ids
XXX06	1
YYY01	2
ZZZ06	3

/*Macro - to get todays date */
%let todaysDate = %sysfunc(today(), yymmdd10.);

%put &todaysDate;

%macro id;

proc sql noprint;
select count(distinct(ids))into :a
from test 
;
quit;

%let a = &a;

%do k = 1 %to &a;

%LET identify = &k;

proc sql noprint;
select distinct id into :client_id
from test
where ids = &identify;

quit;

%let client_id = &client_id;
%put &client_id ;

proc sql ;
CREATE TABLE &client_id_CUSTOMER_HEADER_&todaysDate as
select
&client_id varchar(16) default null,
unique_identifier bigint default null,
der_hash_code bigint default null,
der_hash_key varchar(50) default null,
elements_address_dps varchar(2) default null,
elements_address_status varchar(1) default null,
elements_key_household bigint default null,
elements_key_urn_household bigint default null,
elements_data_date datetime default null,
unique_rec integer default 0
;
quit;

%end;
%mend;
%clientid;

Log ERROR!!!!

MPRINT(CLIENTID): proc sql noprint;
MPRINT(CLIENTID): select count(distinct(ids))into :a from test ;
MPRINT(CLIENTID): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 5850.87k
OS Memory 29560.00k
Timestamp 02/08/2019 12:38:43 PM
Step Count 23 Switch Count 0

MPRINT(CLIENTID): proc sql noprint;
MPRINT(CLIENTID): select distinct shell into :client_id from test where ids = 1;
MPRINT(CLIENTID): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5850.65k
OS Memory 29816.00k
Timestamp 02/08/2019 12:38:43 PM
Step Count 24 Switch Count 0

XXX06
MPRINT(CLIENTID): proc sql ;
WARNING: Apparent symbolic reference CLIENT_ID_CUSTOMER_HEADER_ not resolved.
NOTE: Line generated by the invoked macro "CLIENTID".
75 &client_id_CUSTOMER_HEADER_&todaysDate as select &client_id varchar(16) default null,
_
22
76
75 ! unique_identifier bigint default null, der_hash_code bigint default null, der_hash_key varchar(50)
75 ! default null,
MPRINT(CLIENTID): CREATE TABLE &client_id_CUSTOMER_HEADER_2019-08-02 as select XXX06 varchar(16) default null, unique_identifier
3 The SAS System Friday, 2 August 2019 12:34:00

bigint default null, der_hash_code bigint default null, der_hash_key varchar(50) default null, elements_address_dps varchar(2)
default null, elements_address_status varchar(1) default null, elements_key_household bigint default null,
elements_key_urn_household bigint default null, elements_data_date datetime default null, unique_rec integer default 0 ;
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.

ERROR 76-322: Syntax error, the statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
IMPRINT(CLIENTID): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real-time 0.00 seconds
user CPU time 0.00 seconds
system CPU time 0.00 seconds
memory 40.96k
OS Memory 24172.00k
Timestamp 02/08/2019 12:38:43 PM
Step Count 25 Switch Count 0

[tsap · Accepted solution]

The problem is that SAS doesn't know that your macro is just 'client_id', as there is nothing to tell it where to stop in the text string. So it runs up to the ampersand, making the assumption that the macro name is  '&client_id_CUSTOMER_HEADER_'.

Which is why this warning message appeared in your log.

WARNING: Apparent symbolic reference CLIENT_ID_CUSTOMER_HEADER_ not resolved.

Put a period (.) at the end of your macro name(&client_id), so:  &client_id._CUSTOMER_HEADER_&todaysDate

That should fix your issue with the Client_ID macro not resolving.

Hope this helps

[ballardw]

Is this bit of SQL supposed to be executed remotely?

proc sql ;
CREATE TABLE &client_id_CUSTOMER_HEADER_&todaysDate as
select
&client_id varchar(16) default null,
unique_identifier bigint default null,
der_hash_code bigint default null,
der_hash_key varchar(50) default null,
elements_address_dps varchar(2) default null,
elements_address_status varchar(1) default null,
elements_key_household bigint default null,
elements_key_urn_household bigint default null,
elements_data_date datetime default null,
unique_rec integer default 0
;
quit;

If not you will get errors because VARCHAR, BIGINT, Default and Null are not SAS Proc SQL keywords.

You have to start with working non-macro code and then add the macro elements.