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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: