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