why my code does't work

[soham_sas]

Hi

i am trying to create multiple table from a dataset , so in the data statement i have mentioned all those table name (macro variable table_name) 

i have pasted the log file which is showing some syntax error , please help me to resolve the issue 

23         GOPTIONS ACCESSIBLE;
SYMBOLGEN:  Macro variable TABLE_NAME resolves to ATLANTA BRECKSVILLE CHICAGO CLEVELAND COLUMBUS DETROIT MIDDLEBRG_HTS MOUNT_VERNON 
            PHILA PHILADELPHIA SYLVANIA TOLEDO
24         data &table_name.;
25         set multiple_table;
26         do i=1 to &count.;
SYMBOLGEN:  Macro variable COUNT resolves to       12
27         if ProviderCity = cat('"',scan(&var_names.,i,","),'"') then output scan(&output_name.,i,",");
SYMBOLGEN:  Macro variable VAR_NAMES resolves to "ATLANTA,BRECKSVILLE,CHICAGO,CLEVELAND,COLUMBUS,DETROIT,MIDDLEBRG HTS,MOUNT 
            VERNON,PHILA,PHILADELPHIA,SYLVANIA,TOLEDO"
SYMBOLGEN:  Macro variable OUTPUT_NAME resolves to 
            "ATLANTA,BRECKSVILLE,CHICAGO,CLEVELAND,COLUMBUS,DETROIT,MIDDLEBRG_HTS,MOUNT_VERNON,PHILA,PHILADELPHIA,SYLVANIA,TOLEDO"
27         if ProviderCity = cat('"',scan(&var_names.,i,","),'"') then output scan(&output_name.,i,",");
                                                                                  _
                                                                                  22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, RC, _DATA_, _LAST_, _NULL_.  

27         if ProviderCity = cat('"',scan(&var_names.,i,","),'"') then output scan(&output_name.,i,",");
                                                                                  _
                                                                                  76
ERROR 76-322: Syntax error, statement will be ignored.

28         end;
29         run;

[Astounding]

The OUTPUT statement doesn't allow an expression.  The name of the data set has to be hard-coded.

It would take some rework of your code to get this to work, but it can be done.  You would have to start to defining a macro and calling the macro, because you would need to change from SAS language DO to macro language %DO.  Along those lines, you could code:

%macro make12;

%local k;

data &table_name.;

set multiple_table;

%do k=1 %to &count;

   if ProviderCity = cat('"', scan(&var_names., &k, '",'), '"')

   then output %scan(&output_name., &k, ",");

%end;

run;

%mend make12;

%make12

[ballardw]

A recurring question though is "Why do you want to split the data set?".

A large number of things could be done with the existing set sorted by ProviderCity and then use BY ProviderCity to create summaries, reports or models based on the data for each city.

Of course I also consider use of only a City a dangerous thing as many cities have duplicated names in other states.

[Kurt_Bremser]

The output statement needs a dataset name as its argument. As that dataset is determined at compile time, you can't use an expression there.

Note that by-group processing in data and procedure steps makes splitting of datasets for further processing unnecessary.

If you want to split data for other reasons (like sending data to regional datamarts), you will need macro processing:

%macro split_data;
data &table_name;
set multiple_table;
%do i = 1 %to &count;
if provider_city = "%scan(&table_name,&i)" then output %scan(&table_name,&i);
%end;
run;
%mend;
%split_data

This will create &count if-then statements.