Nested Functions/Do loop with functions in SAS executable statements

[keen_sas]

There are few statements in excel which has to be read into SAS and converted in to SAS dataset and in turn these statements should be converted into SAS executable statements. Below are 3 different statements written in excel which has to be converted into SAS logical statements while passing into data step it should be executed without any errors/warnings.

Scenario1:
else variable = concat(values=var1 " (" var2 ")", delimiter='');
Scenario2:
else variable= concat( values = var1 " (" var2 ")",delimiter='');
Scenario3: concat(values =name1 text2 var3 variable4 temp5, delimiter="; ");

output1 to execute in sas statement:
else variable=catt(var1, " (", var2, ")");

output2 to execute in sas statement:
catt(name1, text2, var3, variable4, temp5); These values are separated by semicolon and space.

  In all of the scenarios we have to concatenate the variables present in the values parameter and these should be separated by delimiter as specified in the statement.Moreover all the variables should be stripped as well, to avoid leading and trailing blanks while concatenating.
In scenario 1 and 2 the variables are separated by space as delimiter and in scenario 3 the values are separated by semicolon and space as delimiter. Another challenge in excel statements there may be unequal spaces before/after values and delimiter , these has to be controlled in SAS executable statements to avoid unwanted errors/warnings.

The CATT can be any of the CAT functions, i have used CATT here temporarily. I have tried with TRANWRD, SCAN, SUBSTR,COMPRESS fucntions to make those SAS executable statements while parsing in data step.Any suggestions/comments greatly appreciated.

[Tom]

In all of the scenarios we have to concatenate the variables present in the values parameter and these should be separated by delimiter as specified in the statement.

So did you try the CATX() function? That is what it does.

[keen_sas]

Hi Tom, The question here is not only using CATX function, I have to remove values = and delimiter from the scenarios and make it executable SAS statement. Please see the scenario 1 &2 and output 1 to get the desired result, what i mean to say .

[Tom]

Given the structure of the calls you posted:

concat(values=var1 " (" var2 ")", delimiter='');
concat( values = var1 " (" var2 ")",delimiter='');
concat(values =name1 text2 var3 variable4 temp5, delimiter="; ");

You might have some success making a macro named CONCAT that has VALUES and DELIMITER as parameters and returns the required text.  Then in your conversion step you could use a call to the RESOLVE() function like this to use it on a particular string.

data test;
   infile cards truncover ;
   input cmd $100. ;
   length newcmd $200 ;
   newcmd = resolve(cats('%',cmd));
cards4;
concat(values=var1 " (" var2 ")", delimiter='');
concat( values = var1 " (" var2 ")",delimiter='');
concat(values =name1 text2 var3 variable4 temp5, delimiter="; ");
;;;;

The macro might be as simple as:

%macro concat(values,delimiter);
%let delimiter=%qsysfunc(dequote(&delimiter));
%if %length(&delimiter) %then
catx(%sysfunc(quote(&delimiter)),of &values)
;
%else
cats(of &values)
;
%mend concat;

Result:

Obs                                   cmd

 1     concat(values=var1 " (" var2 ")", delimiter='');
 2     concat( values = var1 " (" var2 ")",delimiter='');
 3     concat(values =name1 text2 var3 variable4 temp5, delimiter="; ");

Obs    newcmd

 1     cats(of var1 " (" var2 ")");
 2     cats(of var1 " (" var2 ")");
 3     catx("; ",of name1 text2 var3 variable4 temp5);

[Tom]

Are you asking for help understanding what SAS code could do similar operations to the example code you posted? 

If so then please include some test data and show what the results from that test data should look like.  Post data in the form of a SAS data step so that others can re-create the data on their version of SAS and test their answers.

Or  are you asking for help creating a program that can parse the code and convert it? 

If so then show examples of the input code and what output code you want to generate.

[keen_sas]

I am looking for the program that can parse the code and convert it in to SAS executable code in data step after converting the excel statements into SAS executable statements 

S.no	Scenario	SAS Executable statement	Challenge
1	variable = concat(values=var1 " (" var2 ")", delimiter='');	variable=catt(var1, " (", var2, ")");	While removing values = using tranwrd function , sometimes there are extra spaces present after values or may not be present ( same with delimiter as well) , this cannot be handled by tranwrd function.
2	variable=concat(values =name1 text2 var3 variable4 temp5, delimiter="; ");	Variable=catt(name1, text2, var3, variable4, temp5);	Here the delimiter is semicolon and space , each variable value are separated by semicolon and space.

Converting the SAS executable statement into macro variable and then passing that into data step to execute those statements with out any errors/warnings

data test ;
set test;
%do d = 1 %to &din ;
&&disp&d;
%end;
run ;

[Tom · Accepted solution]

You might want to supply a more realistic example that we can actually test.

But you could perhaps do this with a macro.

So your conversion step will be to just use TRANWRD() function to convert 'CONCAT(' to '%CONCAT('.  Might need to make it little more complicated if some of them have mixed case or spaces between CONCAT and open parenthesis.

I notice that the OF operator does not work when some of the values are string constants instead of variable names. 

So here is a more complicated macro definition that turns VALUES into a comma delimited list.

%macro concat(values,delimiter);
%local i sep;
%if %length(%qsysfunc(dequote(&delimiter))) %then catx(&delimiter, ;
%else catt( ;
%do i=1 %to %sysfunc(countw(%superq(values),%str( ),q));
  &sep.%scan(%superq(values),&i,%str( ),q)
  %let sep=,;
%end;
)
%mend concat;

So let's make up some sample data and test whether the macro works.

options mprint;
data test;
  input var1 var2 name1 $ text2 $ var3 variable4 temp5 $ ;
  length x1-x3 $100;
 x1=%concat(values=var1 " (" var2 ")", delimiter='');
 x2=%concat( values = var1 " (" var2 ")",delimiter='');
 x3=%concat(values =name1 text2 var3 variable4 temp5, delimiter="; ");
  put (_all_) (=/);
cards;
1 2 n1 t1 3 4 t5
;

Results look good.

312  options mprint;
313  data test;
314    input var1 var2 name1 $ text2 $ var3 variable4 temp5 $ ;
315    length x1-x3 $100;
316   x1=%concat(values=var1 " (" var2 ")", delimiter='');
MPRINT(CONCAT):  catt( var1 ," (" ,var2 ,")" )
317   x2=%concat( values = var1 " (" var2 ")",delimiter='');
MPRINT(CONCAT):  catt( var1 ," (" ,var2 ,")" )
318   x3=%concat(values =name1 text2 var3 variable4 temp5, delimiter="; ");
MPRINT(CONCAT):  catx("; ", name1 ,text2 ,var3 ,variable4 ,temp5 )
319    put (_all_) (=/);
320  cards;


var1=1
var2=2
name1=n1
text2=t1
var3=3
variable4=4
temp5=t5
x1=1 (2)
x2=1 (2)
x3=n1; t1; 3; 4; t5
NOTE: The data set WORK.TEST has 1 observations and 10 variables.

[keen_sas]

Hi Tom,
Thanks for the solution and suggestion you have provided, its working fine.
My scenarios are increasing day to day, previously it was only the below scenario and you have provided a perfect solution with RESOLVE function.
variable = concat(values=var1 " (" var2 ")", delimiter=''); x1=%concat(values=var1 " (" var2 ")", delimiter='');
My query is can the resolve function works in between the sentence anywhere in the if then else condition like below example

Scenario1: if age=10 then new=var;
else new =concat(values=var1 " (" var2 ")", delimiter='');

Scenarion2 ;

if age=20 then new=concat(values=var1 " (" var2 ")", delimiter='')";
else text=concat(values=var3 " (" var4 ")", delimiter='');

How to make resolve function work anywhere in between the sentence, not only at the starting position.

[Reeza]

Are you trying to write a parser that converts a different language to SAS?