SAS: Create SAS Table from List of Strings

Normally I need to compile a unique list of text strings from SAS table and run report for each item in the list through a %do loop. See details in the previous post. In this case, I need to convert the list of strings back to SAS table using space as delimiter.

Solution

%let lst = apple orange grape carrot banana watermelon peach;
%macro createtbl;
data lst; 
length fruit $15.;
%let i = 1;
%do %while (%scan(&lst., &i., ' ') ne );
  fruit = "%scan(&lst., &i. , ' ')"; 
  output;
%let i = %eval(&i. +1);
%end; 
run;
%mend createtbl;
%createtbl;

Notes

  • Have to use macro with %macro and %mend because %do, %end, and %eval are not valid in open code. Macros with %macro and %mend are closed marcos.
  • Use single quotation ‘ ‘ for space and use double quotation ” ” for string. The other way will not work. In the above example, if I change the equation for fruit to ‘%scan(&lst., &i. , ” “)’, then the string %scan(&lst., &i., ” “) becomes the values in the fruit field without interpreting the macro variable in the equation and applying the scan function.
  • Need %let i = %eval(&i. +1) with the %do % while statement

Wrong Code Demo and Error Diagnosis

data lst; 
length fruit $15.;
do i =1 to 7;
  fruit = "%scan(&lst., i , ' ')"; 
  output;
end;
run;

Error Message

  • ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric
  • operand is required. The condition was: i
  • ERROR: Argument 2 to macro function %SCAN is not a number.

Diagnosis

  • The second argument i is not a number. To solve this, have to use closed macro and %eval, so the value of i +1 can be calculated.

Other Methods that don’t work

  • Create empty table first and then add values by row.
  • Don’t know how to add values by row without using macro.
/* NOT USEFUL*/
data lst;
attrib 
    fruit length=$15 format=$15. label="fruit";
stop;
run;