SAS Macro Variable Quoting

#1
I need help with macro variable quoting.
I have a SAS macro variable that has a list of values separated by | as below. The values are gathered from a SAS Dataset.

Proc Sql;
Select Distinct Strip(Diseasename) into :Dlist Separated by '|' From Dis_List;
Quit;

%Put %Bquote(&DList);

Anal Cancer|Bladder Cancer|Bone & Connective Tissue Cancer|Brain & Nervous System Cancer|Breast Cancer|Colorectal Cancer|Gastrointestinal Cancers; Other|Genitourinary Cancer|Gynecological Cancer|Head & Neck Cancer|Hodgkin's Disease|Kidney Cancer,
Other|Leukemia|Liver Cancer|Lung Cancer|Malignant Melanoma|Malignant Neoplasms; Other|Multiple Myeloma|Myelodysplastic Syndrome|Non-Hodgkin's Lymphoma|Ovarian Cancer|Pancreatic Cancer|Prostate Cancer|Renal Cell|Secondary Malignant Neoplasm|Skin Cancer;

I have to execute a macro program for each value in the abovle list.
The problem is, there are values that have single quotes (') in them.
e.g: Hodgkin's Disease, Non-Hodgkin's Lymphoma

I am having trouble with the above two values.

I have similified the code as below:

%Let Cnt=26; (Number of Values).
%Macro Test;
%Do I = 1 %To &Cnt;
%Put ITR: &I.;
%Put COND: %Scan(%BQuote(&DList.), &I., '|');
%End;
%Mend;

%Test;

The log is as follows (partial log)

ITR: 9
COND: Gynecological Cancer
ITR: 10
COND: Head & Neck Cancer
ITR: 11
COND: Hodgkin
ITR: 12
COND: s Disease
ITR: 13
COND: Kidney Cancer, Other
......
........

ITR: 21
COND: Non-Hodgkin
ITR: 22
COND: s Lymphoma
ITR: 23
COND: Ovarian Cancer

As you can see ITR 11, 12 and 21, 22 are not correct (infact 11 & 12 is a single value and 21 and 22 is a single value).

The macro i wrote seems to split the Hodgkin's Disease into two values bacause of the unmatched quote.

Can anyone please help me to get around this issue!!!!
 
#2
Hi
instead of BQuote function try using the compress fuction which will help u to remove the unbalanced quotation marks
%Macro Test;
%Do I = 1 %To &Cnt;
%Put ITR: &I.;
%Put COND: %Scan(%sysfunc(compress("&DList","'")), &I., '|');
%End;
%Mend;

%Test;


Try the above code and let me know if u got wat u wanted

Thanks,
Saranya