In log I am receiving an Error saying ERROR 13-12: Unrecognized SAS option name SQL.

[nxmogil]

options Mprint Mlogic Symbolgen 

%MACRO Testw (Dsn=, out=, Split_var=);
proc sql;
select distinct &Split_var into : Split_values separated by ' '
from &Dsn;
quit;

%let i = 1;
%do %while(%scan (&Split_var, &i) ne );
%let Split_values =  %scan(&split_values, &i);
data &out._&Split_values;
set &Dsn
where &Split_var = = "&split_value";
run;
%let i = %eval(&i+1);
%end;
%put Splitting complete;
%mend;
%Testw(Dsn=sashelp.class, out=Gender, Split_var=sex);

[PaigeMiller]

You are missing a semi-colon on one of your SAS statements. Please look carefully, I'm sure you will find it. (Actually you have at least two missing semi-colons, but only the first such omission is causing this error).

By the way, splitting data sets this way is rarely a good thing to do, it simply makes programming whatever the next step much more difficult. And for some people, as you have found, getting the macro to work isn't that simple. Why are you writing a macro to split this data set? What will you do with the splits, what is the next step after running this macro?

[ballardw · Accepted solution]

You want to think real hard about what this line is actually doing to your existing variable and what happens after:

%let Split_values =  %scan(&split_values, &i);

Real hard.

As in you just destroyed the list of values in &split_values.

One strongly suspects that you meant to use to get one from the list

%let Split_value =  %scan(&split_values, &i);

And if you hadn't destroyed &split_values this would not work as likely intended as &split_values is a space delimited list and gets a value like "gender_F M"

data &out._&Split_values;

Which brings up a question, is &out supposed to be a library or just a data set prefix.

You may want to consider using an iterated list instead of the do while:

/* if you feel a need to count &sqlobs after the SQL, when it runs correctly, will have the count of items in the list*/*/
%do i= 1 %to &sysfunc(countw(&split_values));
%let Split_value = %scan(&split_values,&i);

NE and the macro language can be a bit touchy for some things.

[Kurt_Bremser]

Instead of creating a sequence of DATA steps, create a single step which has all datasets in the DATA statement (first %DO loop) and then uses a SELECT block to output to the respective targets (second %DO loop). This way you read the source dataset only twice.

[nxmogil]

Hi All, Thank you for the Suggestions and Corrections. I was able to correct it.