Proc FCMP with Table-Based Waterfall (NOQUOTELENMAX in PROC FCMP)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Proc FCMP with Table-Based Waterfall (NOQUOTELENMAX in PROC FCMP)

[ Edited ]

We're migrating processes from Access to SAS. Within Access, our queries use a VBA user-defined function which accepts two arguments then conditionally finds a certain value based on argument 1 and substrings of argument 2. Basically the VBA is a giant waterfall of conditions and keeps checking the condition statements until it meets one 100% then returns the value. I've successfully transitioned this user function into SAS using PROC FCMP with hundreds of if-then statements. So it works for now, however, because all of this is coded, making updates to the function takes considerable time and opens the process to risk. So I'm trying to move this to a table-based solution which will be easily and safely updated. At the moment, I have a dataset in SAS that has the same waterfall as the user function. In testing, I've been able to successfully return the correct value from the table using DATA STEP, PROC SQL, and even reformatting the table into a string then searching the string until the conditions are satisfied. But with each attempt when I try to move the approach into PROC FCMP I'm met with issues. I believe that I am the closest and the process would be the fastest with the string-based solution. At the moment, I'm writing the table to a string then storing the string in a text file with a %LET statement to assign it to a variable with this code:

 

/*CREATE STRING*/
proc sql;
select NewCol into: strKV separated by ' ' from Renamed_KV;
quit;
/*WRITE FILE*/
data _null_;
file '/users/apps/udfs/strKV.sas';
put '%let KV=';
put "&strKV.;";
run;

 

The file is 17,513 characters long and looks like this:

%let KV=
A<Archive>R<Paper> A<RETAILER>R<Retail> A<REPOSITORY>R<Retail> A<DISPUTES>R<Disputes> 
[portion removed]
A<ELSE>B<FX>K<ELSE>N<8471>R<ABCD> A<ELSE>B<FX>K<ELSE>N<8988>R<ABCDE> A<ELSE>B<FX>K<ELSE>N<8991>R<ABCDEF>;

 

The code searches for "A<" then examines if the A value matches argument 1. If it does it then examines any values for B-Q to see if certain substrings of argument 2 match those values if they exist. If they do, it returns the R value, if not it finds the next A value and tries again.

 

My problem comes up when moving it to PROC FCMP. Here's my full code:

	LIBNAME dmudfs '/users/apps/udfs/';

	options NOQUOTELENMAX;
	proc fcmp outlib = dmudfs.UDF2.UDF2;
			
	function sKVol(BN, BC);
	options NOQUOTELENMAX;

	%include '/users/apps/udfs/strKV.sas';
	length sKV $32767;
	sKV = &KV.;					/*option1 NO QUOTES*/
	*sKV = cat("'", &KV., "'"); /*option2 SINGLE QUOTES CONCATENTATION*/
	*sKV = "&KV.";				/*option3 DOUBLE QUOTES*/
	
		/*FIND RECORD*/
		RcdStart = 0; /*Initialize Value*/
		NextRecord:
			RcdStart = RcdStart + 1; /*Increment starting position*/
			/*FIND RECORD START*/		
			APos = find(sKV, cat("A<", BC), 't', RcdStart);
			if APos = 0 then APos = find(cat("'",sKV,"'"), "A<ELSE>", 't', RcdStart);
			RcdStart = APos;
			/*ISOLATE RECORD*/
			RcdEnd = find(sKV, "A<",'t', RcdStart+1);
			if RcdEnd = 0 then RcdEnd = length(sKV);
			Record = substr(sKV, RcdStart, RcdEnd-RcdStart);

			/*REVIEW BATCHNAME*/
			i=1;
			NextAlpha:
				/*FIND ALPHA EQUIVALENT*/
				i=i+1;
				if i < 27 then 			Alpha = byte(rank('@') + i);
				else if i < 703 then 	Alpha = catt(byte(rank('@') + floor((i-1) / 26)), byte(rank('@') + mod(i-1, 26) + 1));
				else 					Alpha = catt(byte(rank('@') + floor((i-1) / 702)), byte(rank('@') + floor(mod(i-1, 702) / 26) + 1), byte(rank('@') + mod(mod(i-1, 702),26) + 1));
				call symputx('Alpha', Alpha);
			
				/*FIND ALPHA IN RECORD*/
				ALPHAstart = find(record, cat(Alpha, "<"),'t', 1);
				/*IF ALPHA EXISTS*/
				if ALPHAstart > 0 then do;
					ALPHAend = find(record, ">",'t', ALPHAstart+1);			/*FIND END*/
					ALPHArcd = substr(record, ALPHAstart+2, ALPHAend-ALPHAstart-2); /*ISOLATE*/

					if Alpha = 'R' then go to Done; 						/*FINISHED*/
					if ALPHArcd = 'ELSE' then go to NextAlpha;				/*NEXT ALPHA*/
					if Alpha = 'B' and ALPHArcd = substr(BN, 1, 2) then go to NextAlpha; 
					if Alpha = 'C' and ALPHArcd = substr(BN, 1, 3) then go to NextAlpha; 
					if Alpha = 'D' and ALPHArcd = substr(BN, 1, 4) then go to NextAlpha; 
					if Alpha = 'E' and ALPHArcd = substr(BN, 1, 5) then go to NextAlpha; 
					if Alpha = 'F' and ALPHArcd = substr(BN, 1, 6) then go to NextAlpha; 
					if Alpha = 'G' and ALPHArcd = substr(BN, 1, 7) then go to NextAlpha; 
					if Alpha = 'H' and ALPHArcd = substr(BN, 7, 2) then go to NextAlpha; 
					if Alpha = 'I' and ALPHArcd = substr(BN, 7, 3) then go to NextAlpha; 
					if Alpha = 'J' and ALPHArcd = substr(BN, 7, 4) then go to NextAlpha; 
					if Alpha = 'K' and ALPHArcd = substr(BN, 8, 2) then go to NextAlpha; 
					if Alpha = 'L' and ALPHArcd = substr(BN, 8, 3) then go to NextAlpha; 
					if Alpha = 'M' and ALPHArcd = substr(BN, 10, 2) then go to NextAlpha; 
					if Alpha = 'N' and ALPHArcd = substr(BN, 14, 4) then go to NextAlpha; 
					if Alpha = 'O' and ALPHArcd = substr(BN, 16, 4) then go to NextAlpha; 
					if Alpha = 'P' and ALPHArcd = substr(BN, Length(BN)-1,2) then go to NextAlpha; 
					if Alpha = 'Q' and ALPHArcd = substr(BN, Length(BN)-2,3) then go to NextAlpha; 
					go to NextRecord;
					end;
				/*IF ALPHA DOESNT EXIST*/
				if ALPHAstart = 0 then go to NextAlpha;

			/*FINISHED*/
			Done:

		return(ALPHArcd);
		options QUOTELENMAX;
		endsub;
	run;

 

 

The problem comes when I try to write &KV to a value in the program. I've tried 3 approaches.

 

Problem with /*option1 NO QUOTES*/

When I reference &KV the code sees the < and > signs and tries to perform a calculation. Previously, I had used [ and ] but then it was trying to define my values as arrays.

26         	sKV = &KV.;					/*option1 NO QUTOES*/
NOTE: Line generated by the macro variable "KV".
26          A<Archive>R<Paper> A<RETAILER>R<Retail> A<REPOSITORY>R<Retail>
               ___________
               22
               76
[portion removed]
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, LE, LT, NE, OR, ^<, ^=, ^>, |, ||, ~<, ~=, ~>. ERROR 76-322: Syntax error, statement will be ignored.

 

 

Problem with /*option2 SINGLE QUOTES CONCATENTATION*/

This is the same problem as option1, it tries to perform calculations.

27         	sKV = cat("'", &KV., "'"); /*option2 SINGLE QUOTES CONCATENTATION*/
NOTE: Line generated by the macro variable "KV".
27          A<Archive>R<Paper> A<RETAILER>R<Retail> A<REPOSITORY>R<Retail>
               ___________
               22
               76
[portion removed]
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, LE, LT, NE, OR, ^<, ^=, ^>, |, ||, ~<, ~=, ~>. ERROR 76-322: Syntax error, statement will be ignored.

 

 

Problem with /*option3 DOUBLE QUOTES*/

When I try to place the value into a string I get an error because the string is too long despite my use of options NOQUOTELENMAX. I'm not sure where it needs to go with PROC FCMP or even if it can be used at all with PROC FCMP so I placed it in two separate places. I receive this error:

28         	sKV = "&KV.";				/*option3 DOUBLE QUOTES*/
ERROR: A quoted string must not exceed 256 characters in length.

 

 

 

Is there a way to write a string longer than 256 in PROC FCMP?

Or is there a way that I can use FIND() on the file without writing &KV to sKV?

 

I'm running SAS Enterprise Guide Version 5.1 (5.100.0.13304) Hot fix 17 (32-bit) on Windows 7.


Accepted Solutions
Solution
‎09-28-2016 09:57 AM
Occasional Contributor
Posts: 14

Re: Proc FCMP with Table-Based Waterfall (NOQUOTELENMAX in PROC FCMP)

I was finally able to get this to work by embedding a data step within a macro then using run_macro within PROC FCMP to call the macro. After testing, however, the macro+data-step approach was anywhere from 70-100x slower than the function we'd already built using hundreds of IF-THEN statements.

 

So, I've changed my approach and am using the table to generate the IF-THEN statements and ultimately run the PROC FCMP. This approach allows simple updates to the table and quick updates to the PROC FCMP function even though the function's code is a bear to look at.

 

Here's the code to build the functions from the dataset. It actually builds 6 different functions as we need the function to return different results in different scenarios.

/*GET LINE COUNT*/
%let dsid=%sysfunc(open(dmdata.KV)); %let MaxRow=%sysfunc(attrn(&dsid,nobs)); %let rc=%sysfunc(close(&dsid));

/*BUILD LINES OF CODE*/
data Temp;
	set dmdata.kv;
	length 	start $7 Code1 $256 Code2 $256 Code3 $256 Code4 $256 Code5 $256 Code6 $256;
	keep 	Code1 Code2 Code3 Code4 Code5 Code6 ;
	if _n_ = 1 then start = 'if'; else start = 'else if';
	if BatchClass ne 'ELSE' then BatchClassa=cats('BatchClass="', BatchClass, '" '); else BatchClassa='' ;
	if S0L2 ne '' and S0L2 ne 'ELSE' then S0L2a=cats(' and S0L2="',S0L2,'"'); else S0L2a='';
	if S0L3 ne '' and S0L3 ne 'ELSE' then S0L3a=cats(' and S0L3="',S0L3,'"'); else S0L3a='';
	if S0L4 ne '' and S0L4 ne 'ELSE' then S0L4a=cats(' and S0L4="',S0L4,'"'); else S0L4a='';
	if S0L5 ne '' and S0L5 ne 'ELSE' then S0L5a=cats(' and S0L5="',S0L5,'"'); else S0L5a='';
	if S0L6 ne '' and S0L6 ne 'ELSE' then S0L6a=cats(' and S0L6="',S0L6,'"'); else S0L6a='';
	if S0L7 ne '' and S0L7 ne 'ELSE' then S0L7a=cats(' and S0L7="',S0L7,'"'); else S0L7a='';
	if S7L2 ne '' and S7L2 ne 'ELSE' then S7L2a=cats(' and S7L2="',S7L2,'"'); else S7L2a='';
	if S7L3 ne '' and S7L3 ne 'ELSE' then S7L3a=cats(' and S7L3="',S7L3,'"'); else S7L3a='';
	if S7L4 ne '' and S7L4 ne 'ELSE' then S7L4a=cats(' and S7L4="',S7L4,'"'); else S7L4a='';
	if S8L2 ne '' and S8L2 ne 'ELSE' then S8L2a=cats(' and S8L2="',S8L2,'"'); else S8L2a='';
	if S8L3 ne '' and S8L3 ne 'ELSE' then S8L3a=cats(' and S8L3="',S8L3,'"'); else S8L3a='';
	if S10L2 ne '' and S10L2 ne 'ELSE' then S10L2a=cats(' and S10L2="',S10L2,'"'); else S10L2a='';
	if S14L4 ne '' and S14L4 ne 'ELSE' then S14L4a=cats(' and S14L4="',S14L4,'"'); else S14L4a='';
	if S16L2 ne '' and S16L2 ne 'ELSE' then S16L2a=cats(' and S16L4="',S16L2,'"'); else S16L2a='';
	if S16L4 ne '' and S16L4 ne 'ELSE' then S16L4a=cats(' and S16L4="',S16L4,'"'); else S16L4a='';
	if r2 ne '' and R2 ne 'ELSE' then r2a=cats(' and r2="',r2,'"'); else r2a='';
	if r3 ne '' and R3 ne 'ELSE' then r3a=cats(' and r3="',r3,'"'); else r3a='';
	ReturnA1 = cats('"',Return1,'";');
	ReturnA2 = cats('"',Return2,'";');
	ReturnA3 = cats('"',Return3,'";');
	ReturnA4 = cats('"',Return4,'";');
	ReturnA5 = cats('"',Return5,'";');
	ReturnA6 = cats('"',Return6,'";');
	Code1 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA1),"if and","if"),"if then","if");
	Code2 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA2),"if and","if"),"if then","if");
	Code3 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA3),"if and","if"),"if then","if");
	Code4 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA4),"if and","if"),"if then","if");
	Code5 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA5),"if and","if"),"if then","if");
	Code6 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA6),"if and","if"),"if then","if");
	if _n_ = &MaxRow. then do;
		Code1 = tranwrd(Code1,"if","");
		Code2 = tranwrd(Code2,"if","");
		Code3 = tranwrd(Code3,"if","");
		Code4 = tranwrd(Code4,"if","");
		Code5 = tranwrd(Code5,"if","");
		Code6 = tranwrd(Code6,"if","");
	end;
run;



/*BUILD MACRO FILE1*/
%let sFile = /users/apps/BuildKofaxVolUDF.sas;
data _null_;
	set Temp;
	file "&sFile.";
	if _n_ = 1 then do;
		put '%macro MakeFCMP;';
		put "LIBNAME dmudfs '/users/apps/udfs/';";
		put "proc fcmp outlib=dmudfs.testKofaxVol.testKofaxVol;";
		put "Function KV1 (BatchName $, BatchClass $)$ 32767;";
		put "length KV $32767;";
		put "S0L2 = substr(BatchName, 1, 2);";
		put "S0L3 = substr(BatchName, 1, 3);";
		put "S0L4 = substr(BatchName, 1, 4);";
		put "S0L5 = substr(BatchName, 1, 5);";
		put "S0L6 = substr(BatchName, 1, 6);";
		put "S0L7 = substr(BatchName, 1, 7);";
		put "S3L2 = substr(BatchName, 3, 2);";
		put "S7L2 = substr(BatchName, 7, 2);";
		put "S7L3 = substr(BatchName, 7, 3);";
		put "S7L4 = substr(BatchName, 7, 4);";
		put "S8L2 = substr(BatchName, 8, 2);";
		put "S8L3 = substr(BatchName, 8, 3);";
		put "S8L4 = substr(BatchName, 8, 4);";
		put "S10L2 = substr(BatchName, 10, 2);";
		put "S14L4 = substr(BatchName, 14, 4);";
		put "S16L2 = substr(BatchName, 16, 2);";
		put "S16L4 = substr(BatchName, 16, 4);";
		put "R3 = substr(Batchname, length(BatchName)-2, 3);";
		put "R2 = substr(Batchname, length(BatchName)-1, 2);";
	end;
	put Code1;
	if _n_ = &MaxRow. then do;
		put "return(KV);";
		put "endsub;";
		put "run;";
		put '%mend MakeFCMP;';
	end;
run;

/*EXECUTE MACRO1*/
data _null_; %include "&sFile."; %MakeFCMP; run;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Proc FCMP with Table-Based Waterfall (NOQUOTELENMAX in PROC FCMP)

I am not going to claim to have spent much time looking at your code. I often find that approaches from other applications need not be slavishly transferred to SAS. For example you are performing a look up at heart. So I often think of custom formats as one way to do that.

I have a very small example that builds a Cntlin data set from some limited data and then an example that uses the format from a couple of variables. Note that I did not spend any time with lengths of variables and the displayed text is pretty rudimentary. I would hope from your existing data (use the Access database) you could build the equivalent of the data set I call Temp below to get a better Label variable. I did all of this in one format, your work may benefit from multiple formats. The inserted hyphen is one aproach to differentiate between looking for a-bc and ab-c as two strings. I did not address case of letters.

The structure and contents of a Cntlin data set aren't that fancy and the CNTLOUT option lets you find the variable names and content from a manually created format. The HLO variable contains a number of options, the O is for "other" and value not specifically listed.

Data ex1;
   input string1 $ ;
datalines;
ab
bc
ef
gh
;

data ex2;
   input string2 $;
datalines;
j
je
jef
ma
mar
;
run;

proc sql;
   create table temp as
   select cats(string1,'-',string2) as start, catx(' ','Found',string1,'and',string2) as label
   from ex1,ex2;
quit;
data tclin;
   set temp end=last;
   FmtName="Matcher";
   type='C';
   length HLO $ 11;
   if last then do;
      output;
      start="";
      LABEL="No match";
      HLO ="O";
      output;
   end;
   else output;
run;

proc format library=work cntlin=tclin cntlout=work.cntlout;
run;

/* and to use the format */
data example;
   input var1 $ var2 $;
   length temp string1 string2 string3 $25;
   /* two characters from var 1 and 1 from var2*/
   temp = cats(substr(var1,1,2),'-',substr(var2,1,1));
   string1 = put(temp,$matcher.);
   /* two characters from var 1 and 1 from var2*/
   temp = cats(substr(var1,1,2),'-',substr(var2,1,2));
   string2 = put(temp,$matcher.);
   /* two characters from var 1 and 1 from var2*/
   temp = cats(substr(var1,1,2),'-',substr(var2,1,3));
   string3 = put(temp,$matcher.);
datalines;
about jerry
about mike
about mary
effect jerry
effect john
effect mike
ghost martin
ghost fred
;
run;

Maintaince of the format would be relatively easy by UPDATING the existing Cntlin data set with new start and label values and re-running proc format.

 

It may help to put the format in a permanent library and set the FMTSEARCH path to find that library in your work environment. I tend to put formats in the same library as the data they are used with.

Occasional Contributor
Posts: 14

Re: Proc FCMP with Table-Based Waterfall (NOQUOTELENMAX in PROC FCMP)

Thanks, ballardw. However, I just can't see how I could use a format in this situation.

 

Since the original post, I've also tried a technique which writes each observation from the original table to a different macro variable (KV{1,2,3,..}) and then I am writing the list of variables to my text file like this:

%let MaxRow=384;
%let KV1=A[Archive]R[Paper];
%let KV2=A[RETAILER]R[Retail];
%let KV3=A[REPOSITORY]R[Retail];
[etc etc]

In testing this in a data step, things work excellent. But again, like all my previous attempts, when I migrate to PROC FCMP I'm met with numerous errors. With this newest approach, I use this bit of code to cycle through my KV variables with GO TO's pointed at NextRecord when needed.

 

		r=0;
		NextRecord:
			r = r + 1;
			Record = symget("KV" || strip(r));

 

 

However, in PROC FCMP, the same code returns this error:

ERROR: An illegal argument is used in the function call in function 'SYMGET' in statement number 4 at line 7 column 6.
       The statement was:
    0     (7:6)      Record = SYMGET( "KV" || STRIP( Move C<=N(r=1) ) )
ERROR: Exception occurred during subroutine call.

 

 

If there's a way to get symget or something of the like to work within PROC FCMP, then I'll be set. Any thoughts on SYMGET within PROC FCMP?

Solution
‎09-28-2016 09:57 AM
Occasional Contributor
Posts: 14

Re: Proc FCMP with Table-Based Waterfall (NOQUOTELENMAX in PROC FCMP)

I was finally able to get this to work by embedding a data step within a macro then using run_macro within PROC FCMP to call the macro. After testing, however, the macro+data-step approach was anywhere from 70-100x slower than the function we'd already built using hundreds of IF-THEN statements.

 

So, I've changed my approach and am using the table to generate the IF-THEN statements and ultimately run the PROC FCMP. This approach allows simple updates to the table and quick updates to the PROC FCMP function even though the function's code is a bear to look at.

 

Here's the code to build the functions from the dataset. It actually builds 6 different functions as we need the function to return different results in different scenarios.

/*GET LINE COUNT*/
%let dsid=%sysfunc(open(dmdata.KV)); %let MaxRow=%sysfunc(attrn(&dsid,nobs)); %let rc=%sysfunc(close(&dsid));

/*BUILD LINES OF CODE*/
data Temp;
	set dmdata.kv;
	length 	start $7 Code1 $256 Code2 $256 Code3 $256 Code4 $256 Code5 $256 Code6 $256;
	keep 	Code1 Code2 Code3 Code4 Code5 Code6 ;
	if _n_ = 1 then start = 'if'; else start = 'else if';
	if BatchClass ne 'ELSE' then BatchClassa=cats('BatchClass="', BatchClass, '" '); else BatchClassa='' ;
	if S0L2 ne '' and S0L2 ne 'ELSE' then S0L2a=cats(' and S0L2="',S0L2,'"'); else S0L2a='';
	if S0L3 ne '' and S0L3 ne 'ELSE' then S0L3a=cats(' and S0L3="',S0L3,'"'); else S0L3a='';
	if S0L4 ne '' and S0L4 ne 'ELSE' then S0L4a=cats(' and S0L4="',S0L4,'"'); else S0L4a='';
	if S0L5 ne '' and S0L5 ne 'ELSE' then S0L5a=cats(' and S0L5="',S0L5,'"'); else S0L5a='';
	if S0L6 ne '' and S0L6 ne 'ELSE' then S0L6a=cats(' and S0L6="',S0L6,'"'); else S0L6a='';
	if S0L7 ne '' and S0L7 ne 'ELSE' then S0L7a=cats(' and S0L7="',S0L7,'"'); else S0L7a='';
	if S7L2 ne '' and S7L2 ne 'ELSE' then S7L2a=cats(' and S7L2="',S7L2,'"'); else S7L2a='';
	if S7L3 ne '' and S7L3 ne 'ELSE' then S7L3a=cats(' and S7L3="',S7L3,'"'); else S7L3a='';
	if S7L4 ne '' and S7L4 ne 'ELSE' then S7L4a=cats(' and S7L4="',S7L4,'"'); else S7L4a='';
	if S8L2 ne '' and S8L2 ne 'ELSE' then S8L2a=cats(' and S8L2="',S8L2,'"'); else S8L2a='';
	if S8L3 ne '' and S8L3 ne 'ELSE' then S8L3a=cats(' and S8L3="',S8L3,'"'); else S8L3a='';
	if S10L2 ne '' and S10L2 ne 'ELSE' then S10L2a=cats(' and S10L2="',S10L2,'"'); else S10L2a='';
	if S14L4 ne '' and S14L4 ne 'ELSE' then S14L4a=cats(' and S14L4="',S14L4,'"'); else S14L4a='';
	if S16L2 ne '' and S16L2 ne 'ELSE' then S16L2a=cats(' and S16L4="',S16L2,'"'); else S16L2a='';
	if S16L4 ne '' and S16L4 ne 'ELSE' then S16L4a=cats(' and S16L4="',S16L4,'"'); else S16L4a='';
	if r2 ne '' and R2 ne 'ELSE' then r2a=cats(' and r2="',r2,'"'); else r2a='';
	if r3 ne '' and R3 ne 'ELSE' then r3a=cats(' and r3="',r3,'"'); else r3a='';
	ReturnA1 = cats('"',Return1,'";');
	ReturnA2 = cats('"',Return2,'";');
	ReturnA3 = cats('"',Return3,'";');
	ReturnA4 = cats('"',Return4,'";');
	ReturnA5 = cats('"',Return5,'";');
	ReturnA6 = cats('"',Return6,'";');
	Code1 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA1),"if and","if"),"if then","if");
	Code2 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA2),"if and","if"),"if then","if");
	Code3 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA3),"if and","if"),"if then","if");
	Code4 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA4),"if and","if"),"if then","if");
	Code5 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA5),"if and","if"),"if then","if");
	Code6 = tranwrd(tranwrd(catx(" ",start,BatchClassa,S0L2a,S0L3a,S0L4a,S0L5a,S0L6a,S0L7a,S7L2a,S7L3a,S7L4a,S8L2a,S8L3a,S10L2a,S14L4a,S16L2a,S16L4a,r2a,r3a,"then KV=",ReturnA6),"if and","if"),"if then","if");
	if _n_ = &MaxRow. then do;
		Code1 = tranwrd(Code1,"if","");
		Code2 = tranwrd(Code2,"if","");
		Code3 = tranwrd(Code3,"if","");
		Code4 = tranwrd(Code4,"if","");
		Code5 = tranwrd(Code5,"if","");
		Code6 = tranwrd(Code6,"if","");
	end;
run;



/*BUILD MACRO FILE1*/
%let sFile = /users/apps/BuildKofaxVolUDF.sas;
data _null_;
	set Temp;
	file "&sFile.";
	if _n_ = 1 then do;
		put '%macro MakeFCMP;';
		put "LIBNAME dmudfs '/users/apps/udfs/';";
		put "proc fcmp outlib=dmudfs.testKofaxVol.testKofaxVol;";
		put "Function KV1 (BatchName $, BatchClass $)$ 32767;";
		put "length KV $32767;";
		put "S0L2 = substr(BatchName, 1, 2);";
		put "S0L3 = substr(BatchName, 1, 3);";
		put "S0L4 = substr(BatchName, 1, 4);";
		put "S0L5 = substr(BatchName, 1, 5);";
		put "S0L6 = substr(BatchName, 1, 6);";
		put "S0L7 = substr(BatchName, 1, 7);";
		put "S3L2 = substr(BatchName, 3, 2);";
		put "S7L2 = substr(BatchName, 7, 2);";
		put "S7L3 = substr(BatchName, 7, 3);";
		put "S7L4 = substr(BatchName, 7, 4);";
		put "S8L2 = substr(BatchName, 8, 2);";
		put "S8L3 = substr(BatchName, 8, 3);";
		put "S8L4 = substr(BatchName, 8, 4);";
		put "S10L2 = substr(BatchName, 10, 2);";
		put "S14L4 = substr(BatchName, 14, 4);";
		put "S16L2 = substr(BatchName, 16, 2);";
		put "S16L4 = substr(BatchName, 16, 4);";
		put "R3 = substr(Batchname, length(BatchName)-2, 3);";
		put "R2 = substr(Batchname, length(BatchName)-1, 2);";
	end;
	put Code1;
	if _n_ = &MaxRow. then do;
		put "return(KV);";
		put "endsub;";
		put "run;";
		put '%mend MakeFCMP;';
	end;
run;

/*EXECUTE MACRO1*/
data _null_; %include "&sFile."; %MakeFCMP; run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 353 views
  • 0 likes
  • 2 in conversation