CODEWITHSUNDEEP
sassas-macro
mustafghan
mustafghan

Reputation: 171

Using excel sheets from libnames with commas or special characters in them

I have the following code:

%macro area (ds);
data &ds (keep = areaname NSA_Tot SA_Tot);
set ms_File."&ds$"n (firsbobs = 2);
rename F2 = areaname not_seasonally_adjusted = NSA_tot seasonally_adjusted = SA_Tot;
label F2 = ' ' not_seasonally_adjusted = ' ' seasonally_adjusted = ' ';
run;
%mend area;
%area (%str(Coeur d'Alene, ID));

The code will not work because the dataset I have has a comma and apostrophe as part of the dataset name. So the macro doesn't work. How can I fix this?

Basically, with the code above I want the data and the set to resolve to the following:

data Coeur d'Alene, ID;
set ms_File."Coeur d'Alene, ID$"n (firstobs =2);
....

Upvotes: 1

Views: 390

Answers (1)

Joe
Joe

Reputation: 63424

To use these kinds of names, you need options validmemname=extend; set. This is typically off by default.

This works:

options validmemname=extend;

data "Coeur d'Alene, ID"n;
  x=1;
run;

data test;
  set "Coeur d'Alene, ID"n;
run;

If you have options validmemname=compat; set, then your Excel libname engine should translate the names to valid SAS names, usually by replacing invalid characters with underscores, so this would likely work:

options validmemname=extend;

data test;
  set msfile.Coeur_d_Alene__ID;
run;

You could verify by running proc datasets against the libname for your file, or simply browsing it in the explorer.

Upvotes: 3

Related Questions