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;
