Dynamically create if/else if logic from dataset - Code gives a statement not valid Error

[runtodarkhills]

I have multiple ID tables to merge onto large DB (over 100M rows) and joining them with an proc SQL or index is time consuming. Most of these contain under 100 rows so if converted to if/elif logic it runs much faster. These tables can be update so I want this if else if logic to be updated dynamically. 

I have the following script which takes the table and create a "Code" column containing the if/else if logic and then a statement to assign to a macro variable. When I print the macro variable the if else if logic looks perfect. But when I try to include in my code or %put it I get the following error "ERROR 180-322: Statement is not valid or it is used out of proper order." at each else if statement. 

Would there be a simple way to resolve this or a better way to get to the same result? 

%MACRO CREATE_MERGE_LOGIC(LIBRARY, TABLE, KEY, VARIABLE, OUTPUT_VARIABLE);

	DATA TEMP;
	SET &LIBRARY..&TABLE.;
	FORMAT CODE $666.;
	IF _N_ = 1 THEN CODE = CAT("IF ",SYMGET('KEY')," = ",ID," THEN ",SYMGET('OUTPUT_VARIABLE')," = '",TRIM(&VARIABLE.),"';");
	ELSE CODE = CAT("ELSE IF ",SYMGET('KEY')," = ",ID," THEN ",SYMGET('OUTPUT_VARIABLE')," = '",TRIM(&VARIABLE.),"';");
	RUN;

	PROC SQL NOPRINT;
	SELECT CODE FORMAT=$666. 
	INTO : CREATE_CODE SEPARATED BY " "
	FROM TEMP
	;
	QUIT;

%MEND;

[Astounding · Accepted solution]

It would be much easier for you if you could see the code being generated, rather than imagining what it must look like.  So create a file holding the recoding, which you can %include if it looks right:

%MACRO CREATE_MERGE_LOGIC(LIBRARY, TABLE, KEY, VARIABLE, filename);

	DATA _null_;
	SET &LIBRARY..&TABLE.;
	LENGTH CODE $700.;
        file "&filename" lrecl=700;
	IF _N_  > 1 THEN put 'ELSE ' @;
      CODE = CAT("ELSE IF ",SYMGET('KEY')," = ",ID," THEN ",SYMGET('OUTPUT_VARIABLE')," = '",TRIM(&VARIABLE.),"';");
	PUT CODE;
	RUN;

%MEND;

(untested code here, so you will need to do the work at this point)

[runtodarkhills]

Had to slightly change it but works nicely. Also took ballardw feedback and added an initial step that defines the variable format since I was getting things cut off due to character limits.

Final Solution that seems to work nicely

%MACRO CREATE_MERGE_LOGIC(LIBRARY, TABLE, KEY, VARIABLE, OUTPUT_VARIABLE, CODE_LOCATION);

	DATA _null_;
	SET SASHELP.VCOLUMN (WHERE = (LIBNAME = "&LIBRARY." AND MEMNAME = "&TABLE." AND NAME = "&VARIABLE."));
	LENGTH VAR_FORMAT $700.;
    file "&CODE_LOCATION." lrecl=700;
	VAR_FORMAT = CAT('FORMAT ',SYMGET('OUTPUT_VARIABLE'),' ',TRIM(FORMAT),' ;');
	PUT VAR_FORMAT;
	RUN;

	DATA _null_;
	SET &LIBRARY..&TABLE.;
	LENGTH CODE $700.;
        file "&CODE_LOCATION." MOD lrecl=700;
	IF _N_  > 1 THEN put "ELSE " @;
      CODE = CAT('IF ',SYMGET('KEY'),' = ',ID,' THEN ',SYMGET('OUTPUT_VARIABLE'),' = "',TRIM(&VARIABLE.),'";');
	PUT CODE;
	RUN;
%MEND;

[ballardw]

If you provide some sort of demonstration of what you have and what you expect plus the logic involved it would help understand your process/need.

The approach you seem to be attempting looks like it would involve sticking a lot of values into a macro variables and than may have a number of issues, such as memory limits involved for macro variables and precision of values if any of the macro variables are intended to represent numeric values as how you place a numeric value into a macro variable may result in truncation or rounding of values.

My first feeling is that perhaps you could use Format or Informat made using the information what you are calling an "ID table". If your ID tables consist of pairs of values such as a "match on this value" and a "use this value as a result" then they are very likely ideal candidates for Format/Informat use depending on some details.