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;
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
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.