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
... View more