Extract values from macro variable into a variabe in data

[PamG]

I have a set of character values separated by commas in a macro variable.  I would like to create a SAS dataset with each observation taking one value from the macro variable. I run the following code but it gives an error.

%let myNames = "AAA", "BBB", "CCC", "V123";

data names_data;
    length Name $5; 
     do _n_ = 1 to countw(&myNames, ',');
         Name = scan("&myNames", _n_, ',');
         output; 
    end;
run;

[Tom · Accepted solution]

Become your own macro processor to see what the problem is.  Replace the macro variable reference in your code with its value and check if the result is valid SAS code.

data names_data;
    length Name $5; 
     do _n_ = 1 to countw("AAA", "BBB", "CCC", ',');
         Name = scan(""AAA", "BBB", "CCC"", _n_, ',');
         output; 
    end;
run;

So the COUNTW() function call has way too many arguments.

Let's add spaces to the SCAN() function call 

scan(""  AAA  ", "  BBB  ", "  CCC  "", _n_, ',')

to see how it will be parsed.

The first argument consists of alternating string literals and variable names which is not a valid expression.  In addition the variables AAA, BBB, and CCC do not exist.

You can simple use the macro variable to generate the DO statement.

data names_data;
    length Name $5; 
     do Name  = &myNames ;
         output; 
    end;
run;

That will generate this valid SAS code.

data names_data;
    length Name $5; 
     do Name  = "AAA", "BBB", "CCC" ;
         output; 
    end;
run;

[PamG]

Thanks much, Tom.

[ballardw]

You will find that having commas in macro variables is often a problem because that is the separator between values in the macro processor and when such macro variables are used in data step processing you will get similar problems except for the functions that lists of values that are separated by commas. 

Additionally having quote characters as part of the value often complicates things as most functions will expect one value within a set of quotes. You encounter an example with your use of Scan ("&mynames",_n_,',') where the data step will see Scan (""AAA","BBB","CCC","V123"",_n_,','). So the first pair of "" is the empty string to search with everything else problematic as AAA" will not be a valid second parameter because of no comma.

Why was your Myname macro variable defined that way? If only to be parsed by this data step then other definitions will likely work better.