Assign value from macro variable 2

[nickdel]

I've created a macro which returns a single value. how do I implement it within a datastep so as to create a new variable within that datastep. i.e.

data AppendParent;
	set MyInputs;
	format UltimateParent 11.;
	
	UltimateParent = FindUltimateParent(ChildEntity);
run;

So basically I want my 'AppendParent' dataset to contain both the Child and the Parent with the Parent value being returned by the macro 'FindUltimateParent'

Hope that makes sense!

Thanks

Nick

where would we be without rhetorical questions...

 

[ChrisW75]

data AppendParent;
    set MyInputs;
    format UltimateParent 11.;
    
    UltimateParent = %FindUltimateParent(ChildEntity);
run;

Should do it if the macro is set up correctly.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[nickdel]

Chris, using your code appears to invoke the macro but it then error's within the macro itself. However if I just call the macro outside of a datastep it works fine!

macro:

%macro FindUltimateParent(ChildOID);

%let ParentID = &ChildOID;

proc sql noprint;
	select Primary_LE_ID into: NextID
	from MyTable
	where RELN_TYPE = 'OW'
	and Secondary_LE_ID = &ParentID
	and Primary_LE_ID ne Secondary_LE_ID
	and (Date_Ended = . or datepart(Date_Ended) = '31DEC3500'd)
	;
quit;

%if &SQLOBS=0 %then %do;
	%let UltimateParent = &ParentID;
%end;
%else %do;
	%if &ParentID NE &NextID %then %do;
		 %do %while(&SQLOBS >0);
			%let ParentID= &NextID;


						proc sql noprint;
							select Primary_LE_ID into: NextID
							from MyTable
							where RELN_TYPE = 'OW'
							and Secondary_LE_ID = &ParentID
							and Primary_LE_ID ne Secondary_LE_ID
							and (Date_Ended = . or datepart(Date_Ended) = '31DEC3500'd)
							;
						quit;


		%end;
	%end;

	%let UltimateParent = &NextID;

%end;

	%put &UltimateParent;

%mend;

calling it outside the datastep works fine:

%FindUltimateParent(288);

however, calling it within the datastep

data MyInputs;
input ChildEntityID 11.;
datalines;
288
;
run;

data AppendParent;
    set MyInputs;
    format UltimateParent 11.;
    
    UltimateParent = %FindUltimateParent(ChildEntityID);
run;

throws an error at the first sql procedure:

NOTE: Line generated by the invoked macro "FINDULTIMATEPARENT".
1119 proc sql noprint; select Primary_LE_ID into: NextID from MyTable
--- ------- -------------
388 202 395
76
1119! where RELN_TYPE = 'OW' and Secondary_LE_ID = &ParentID and Primary_LE_ID ne
1119! Secondary_LE_ID and (Date_Ended = . or datepart(Date_Ended) =
NOTE: Line generated by the invoked macro "FINDULTIMATEPARENT".
1119 '31DEC3500'd) ; quit;
----
180
LegalEntityOID
ERROR 388-185: Expecting an arithmetic operator.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

ERROR 395-185: Opening parenthesis for SELECT/WHEN expression is missing.

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

ERROR 180-322: Statement is not valid or it is used out of proper order.

Thanks

Nick

where would we be without rhetorical questions...

 

[kdt82]

Hi Nick

You're doing a few things wrong here. First you're trying to treat the macro as a function that returns a value - An issue I had for ages coming from other programming languages.

Although it is possible to emulate functions using macros, you need to make sure that only the value you want returned is not part of macro code. Here you are using proc SQL, so trying to treat it as a function, will return the proc SQL code.

You can however get the above code working by creating a global macro variable and using call execute.

%macro FindUltimateParent(ChildOID);
%global g_UltimateParent;
%let ParentID = &ChildOID;

proc sql noprint;
    select Primary_LE_ID into: NextID
    from MyTable
    where RELN_TYPE = 'OW'
    and Secondary_LE_ID = &ParentID
    and Primary_LE_ID ne Secondary_LE_ID
    and (Date_Ended = . or datepart(Date_Ended) = '31DEC3500'd)
    ;
quit;

%if &SQLOBS=0 %then %do;
    %let UltimateParent = &ParentID;
%end;
%else %do;
    %if &ParentID NE &NextID %then %do;
         %do %while(&SQLOBS >0);
            %let ParentID= &NextID;


                        proc sql noprint;
                            select Primary_LE_ID into: NextID
                            from MyTable
                            where RELN_TYPE = 'OW'
                            and Secondary_LE_ID = &ParentID
                            and Primary_LE_ID ne Secondary_LE_ID
                            and (Date_Ended = . or datepart(Date_Ended) = '31DEC3500'd)
                            ;
                        quit;


        %end;
    %end;

    %let UltimateParent = &NextID;

%end;

    %let g_UltimateParent =  &UltimateParent;

%mend;

Also change the call

data MyInputs;
input ChildEntityID 11.;
datalines;
288
;
run;

data AppendParent;
    set MyInputs;
    format UltimateParent 11.;
    call execute ('%FindUltimateParent('||ChildEntityID||')');
UltimateParent = symget('g_UltimateParent');
run;

HTH

 

[ChrisW75]

Yeah. You need to understand how macro invocation works.
Basically, when you call the macro, it inserts the code within the macro into wherever it was called.
You can't run proc sql within a datastep, basically as soon as SAS sees the "Proc..." statement, it takes that as the end of the current datastep.

I did the same thing once and had to have another much older SAS programmer explain it. It took a while to get it though.

As kdt82 says, call the macro before the datastep and put the result into a macro variable, that'll work.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[nickdel]

Guys, sorry been away for a while! Thanks for the explaination, I think I have been treating SAS macro's incorrectly, shame they dont act like a function, would be extremely useful!

Thanks again

Nick

where would we be without rhetorical questions...

 

[bryanbayfield]

Essentially, MACRO code is code that writes Base code. Just remember that and you won't go far wrong.

So in theis case, you are returning all the Base code that the macro writes to the RHS of the UltimateParent assignment.