How to separate whole sentences within a macro variable?

[Jakobsen96]

Hey guys,

I am trying to produce a report that generates several tables and stores information out of an excel file. So far my code did what I wanted: It produced three tables that contained all the information in just the right format. Unfortunately, after going from my little example data to the real data I realised that my code does not work if my created macro variable "&frage" contains not just single words but whole sentences. Now I am wondering if I have to find another solution for my report. The working code and excel looks like this (excel "test_skalen_items_v2" attached):

proc import
  datafile="yourpath\test_skalen_items_v2.xlsx"
  out=Skalen_Items
  dbms=xlsx
  replace;
run;

proc sql noprint;
  select distinct Frage into :fragen separated by " "
  from Skalen_Items;
quit;

%macro want;

%do i = 1 %to %sysfunc(countw(&fragen.));
%let frage = %scan(&fragen., &i.);

ods pdf file="yourpath\Table.pdf";
ods pdf startpage=now;


data &frage.(drop=Frage);
	set Skalen_Items(where=(Frage="&frage."));
run;

proc report data=&frage.;
	column Variable Antwortoption;
	define Antwortoption / "&frage.";
	compute Antwortoption;
		if Variable = "Kodierung" then call define(_col_ -1, "style", "style={font_weight=bold}");
	endcomp;
run; 

%end;
ods pdf close;
%mend want;
%want;

And if I use the excel "test_skalen_items_FAIL.xlsx" (also attached) the code does not work anymore. As you can see in the Excel, the column "Frage" does now contain not just the single words (e. g. Frage1) but whole sentences (e. g. Frage zwei jo).

To clarify again: Is it possible to create the tables as with my first excel but now with sentences (or: several words with spaces inbetween) or do I have to find a whole other solution for my report? I am thankful for any help!

Kind regards

[ballardw · Accepted solution]

If you separate values by space and allow Countw to look at that value then the spaces are delimiters. Same with Scan or %scan.

From the documentation of Countw:

If you use the COUNTW function with only two arguments, the default delimiters depend on whether your computer uses ASCII or EBCDIC characters.

• If your computer uses ASCII characters, then the default delimiters are as follows:

  blank ! $ % & ( ) * + , - . / ; < ^ |

  In ASCII environments that do not contain the ^ character, the SCAN function uses the ~ character instead.
• If your computer uses EBCDIC characters, then the default delimiters are as follows:

  blank ! $ % & ( ) * + , - . / ; < ¬ | ¢

So you pick a single character that does not appear in the values of your text. You pick. I'm not looking XLSX from unknown sources. Then modify the Countw and Scan to use that character and SEPARATE the values with that character.

proc sql noprint;
  select distinct Frage into :fragen separated by "*"
  from Skalen_Items;
quit;

%macro want;

%do i = 1 %to %sysfunc(countw(&fragen.,*));
%let frage = %scan(&fragen., &i.,*);

[Tom]

Also if you add quotes and use the Q option on COUNTW() and SCAN() then it does not matter if the delimiter is part of a value.

And if you use single quotes to do the quoting it has the added value of protecting any macro triggers, & or %, that are in the strings.

proc sql noprint;
select distinct quote(trim(Frage),"'") into :fragen separated by "*"
  from Skalen_Items
;
quit;

....


%do i = 1 %to %sysfunc(countw(&fragen.,*,q));
  %let frage = %scan(&fragen., &i.,*,q);
  ...
%end;

If you need to remove the quotes use the DEQUOTE() function.

  %let frage = %qsysfunc(dequote(%scan(&fragen., &i.,*,q)));

[Jakobsen96]

Thank you Tom!

[Jakobsen96]

Thank you! For future questions: what kind of format would be save to look at from unknown sources? CSV?