run a SQL in Macro

[Alexxxxxxx]

hello,

Do you know how to run a SQL in Macro?

I am running a macro code

%MACRO merge(variable=);
	data Datastream_&variable. (keep=DATASTREAM_ID &variable.);
		set Work.Datastream_id2;
	run;

	data SP_&variable. (keep=SP_Entity_ID SP_&variable.);
		set WORK.SP_TABLE;
	run;

	proc sql;
		create table Data_SP_&variable. as
		select distinct
		DATASTREAM_ID,
		&variable.,
		SP_Entity_ID
		from Datastream_&variable. as da
		join SP_&variable. as sp on da.&variable.=sp.SP_&variable.
		;
	quit;
%MEND merge;
%merge(variable=CUSIP)
run;

the log is 

NOTE: There were 11853 observations read from the data set WORK.DATASTREAM_ID2.
NOTE: The data set WORK.DATASTREAM_CUSIP has 11853 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.14 seconds
      cpu time            0.01 seconds



NOTE: There were 7946 observations read from the data set WORK.SP_TABLE.
NOTE: The data set WORK.SP_CUSIP has 7946 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.14 seconds
      cpu time            0.03 seconds


NOTE: Table WORK.DATA_SP_CUSIP created, with 0 rows and 3 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

the last dataset cannot be created, but it did not show why it is failed. Could you please give me some suggestions about this?

thanks in advance.

[PaigeMiller · Accepted solution]

This does not seem like a macro problem at all. Most likely, you have not done the join properly, that is why you have zero observations in the DATA_SP_CUSIP. The usual reason you get zero observations is because the ON condition in PROC SQL finds no matches. Can you create code without macros and without macros variables that does the join properly? That is the place to start, that would be the first step in debugging.

Alternatively, you can place the command

options mprint;

at the start of the program and run it again and then look at the code produced in the log and try to debug from that.