how to scan function and bypass multiple delimiters "," "#"

[HitmonTran]

Hi my scan function is giving me and error message:

One of my values have a "," , Respiratory, thoracic and mediastinal disorders' that is causing the error message. How do i make my scan function to continue scanning without giving an error? thanks

proc sql;
select unique(aebodsys) into :aebodsyss separated by '#' from adam.adae where saffl='Y';
quit;

%do i=1 %to &catn;
	%let aebodsys_select = %scan(%str(&aebodsyss), &i., '#');
%end;

[ballardw]

Try %bquote instead of %str in the %scan call.

%macro dummy;
%let value = some text,with comma#other text#another value;

%do i= 1 %to 3;
   %let str= %scan(%bquote(&value.),&i.,#);
   %put str is: &str.;
%end;
%mend;

%dummy

Result:

str is: some text,with comma
str is: other text
str is: another value

You may not want to include the quotes around the delimiter as that makes ' a delimiter for the function.

[HitmonTran]

it was able to continue to scan without log error message, but it didn't output values containing ",".

[ballardw]

---

@HitmonTran wrote:
it was able to continue to scan without log error message, but it didn't output values containing ",".

---

Then it is time to provide an actual example for the macro variable used.

The actual contents do matter when you get into macro quoting functions.

[HitmonTran]

%macro subgroup_tables(subgroup_cond=, subgroup_lbl=, titletext=, order=);

proc sql;
	select unique(aebodsys) into :aebodsyss separated by '#' from adam.adae   ;
	select count(unique(aebodsys)) into :catn from adam.adae   ;
quit;

 
/*loop through the aebodsys and aedecod groups*/
%do i=1 %to &catn;
	%let aebodsys_select = %scan(%bquote(&aebodsyss), &i., '#' );

	%let aedecod = %str();
	proc sql;
		select unique(aedecod) into :aedecods separated by '#' from adam.adae where aebodsys = "&aebodsys_select.";
		select count(unique(aedecod)) into :scatn from adam.adae where  aebodsys = "&aebodsys_select.";
	quit;
 %end;

	%PROP_BINCI(dsn=ae, aedsn=adam.adae, out=aebodsys_&i., whr=%str(aebodsys = "&aebodsys_select."  ), lbl=%str(&aebodsys_select.));

%mend subgroup_tables;
%subgroup_tables(subgroup_cond=, subgroup_lbl=All, titletext=All participants	, order=1)

 data for aebodsys:

[ballardw]

Paste the value as text into a text box opened with the </> icon.

Cannot write code against pictures.

Place a statement like

%put &aebodsyss.;

BEFORE that loop that calls %scan and copy from the log.

[HitmonTran]

Cardiac disorders
Ear and labyrinth disorders
Gastrointestinal disorders
General disorders and administration site conditions
Infections and infestations
Injury, poisoning and procedural complications
Metabolism and nutritiondisorders
Musculoskeletal and connective tissue disorders
Nervous system disorders
Respiratory, thoracic and mediastinal
disorders
Skin and subcutaneous tissue disorders

thank you 

[ballardw]

---

@HitmonTran wrote:

Cardiac disorders
Ear and labyrinth disorders
Gastrointestinal disorders
General disorders and administration site conditions
Infections and infestations
Injury, poisoning and procedural complications
Metabolism and nutritiondisorders
Musculoskeletal and connective tissue disorders
Nervous system disorders
Respiratory, thoracic and mediastinal
disorders
Skin and subcutaneous tissue disorders

thank you 

---

That cannot be the value of your macro variable because there are no # between values as created by

select unique(aebodsys) into :aebodsyss separated by '#' from adam.adae   ;

If that is the actual value of your macro variable aebodsyss then you apparently only had one value for aebodsys in the data set.

You should also show us what the value of &catn is when that loop starts.

BTW where you call

%PROP_BINCI

it would only ever have the LAST value for &aebodsys_select and would be referencing an &i value greater than &catn because you place the call after the end of the %do i= loop.

[HitmonTran]

Yeah the program is pretty complicated, not sure why the previous programmer called 

%PROP_BINCI

within the macro itself, but it works.

here are the values

SYMBOLGEN:  Macro variable AEBODSYSS resolves to Cardiac disorders#Ear and labyrinth disorders#Gastrointestinal disorders#General
            disorders and administration site conditions#Infections and infestations#Injury, poisoning and procedural
            complications#Metabolism and nutrition disorders#Musculoskeletal and connective tissue disorders#Nervous system
            disorders#Respiratory, thoracic and mediastinal disorders#Skin and subcutaneous tissue disorders
24686
24687  %put &aebodsyss;
Cardiac disorders#Ear and labyrinth disorders#Gastrointestinal disorders#General disorders and administration site
conditions#Infections and infestations#Injury, poisoning and procedural complications#Metabolism and nutrition
disorders#Musculoskeletal and connective tissue disorders#Nervous system disorders#Respiratory, thoracic and mediastinal
disorders#Skin and subcutaneous tissue disorders

[ballardw]

When I run this code:

%macro dummy;
%let aebodsyss=Cardiac disorders#Ear and labyrinth disorders#Gastrointestinal disorders#General disorders and administration site
conditions#Infections and infestations#Injury, poisoning and procedural complications#Metabolism and nutrition
disorders#Musculoskeletal and connective tissue disorders#Nervous system disorders#Respiratory, thoracic and mediastinal
disorders#Skin and subcutaneous tissue disorders;

%do i= 1 %to 11;
   %let str= %scan(%bquote(&aebodsyss.),&i.,#);
   %put str &i. is: &str.;
%end;
%mend;

%dummy;

The result is:

str 1 is: Cardiac disorders
str 2 is: Ear and labyrinth disorders
str 3 is: Gastrointestinal disorders
str 4 is: General disorders and administration site conditions
str 5 is: Infections and infestations
str 6 is: Injury, poisoning and procedural complications
str 7 is: Metabolism and nutrition disorders
str 8 is: Musculoskeletal and connective tissue disorders
str 9 is: Nervous system disorders
str 10 is: Respiratory, thoracic and mediastinal disorders
str 11 is: Skin and subcutaneous tissue disorders

So I am not sure what "didn't work" for you.

[Tom · Accepted solution]

If the sub string returned by %SCAN() might contain macro triggers or unbalanced quotes or parentheses then use %QSCAN() instead of %SCAN().  If the original value of the macro variable might have such things then use %SUPERQ() to quote.  You don't have to run the query twice to get the count, PROC SQL counts the number the first time you run it.  Don't include the single quotes  in the delimiter list for %SCAN() or %QSCAN() or else it will treat any single quotes in the string being scanned as a delimiter just like the # character.  Also the UNIQUE keyword in SQL is NOT a function.

Try something like the below cleaned up version of the macro. But I am not sure why you have arguments to the macro that you are not using. Also why are you looping over the strings and doing nothing with the substrings.  If you just want to make the macro variable aebodsys_select have the last substring then use -1 as the index.

  %let aebodsys_select = %qscan(&aebodsyss,-1,#);

Most likely you want to move the call to the sub macro inside the %DO loop, like below.

%macro subgroup_tables(subgroup_cond=, subgroup_lbl=, titletext=, order=);
%local aebodsyss catn i aebodsys_select aedecod scatn;
proc sql noprint;
  select unique aebodsys into :aebodsyss separated by '#' from adam.adae ;
%let catn=&sqlobs;
%let aebodsyss=%superq(aebodsyss);
quit;


/*loop through the aebodsys and aedecod groups*/
%do i=1 %to &catn;
  %let aebodsys_select = %qscan(&aebodsyss,&i,#);
  %let aedecod = ;
proc sql noprint;
  select unique aedecod into :aedecods separated by '#'
    from adam.adae where aebodsys = "&aebodsys_select."
  ;
  %let scatn=&sqlobs;
  %let aedecods=%superq(aedecods);
quit;

  %PROP_BINCI
  (dsn=ae
  ,aedsn=adam.adae
  ,out=aebodsys_&i.
  ,whr= aebodsys = "&aebodsys_select." 
  ,lbl=&aebodsys_select.
  );
%end;

%mend subgroup_tables;

%subgroup_tables
(subgroup_cond=
,subgroup_lbl=All
,titletext=All participants	
,order=1
)

[HitmonTran]

Hi Tom thanks for the answer! it worked for the first half of the program butwhen I tried doing it on part two, something went wrong, could you take a look? thanks

original code:

%macro relrisks();

	proc sql;
		select unique(aebodsys) into :aesocs separated by '#' from adae where aebodsys ne ' ';
		select count(unique(aesoc)) into :aen from adae where aebodsys ne ' ';
	quit;
	%put &aesocs;

	/*loop through AEBODSYS values*/
	%do i=1 %to &aen;
		%let aesoc_select = %scan(%bquote(&aesocs.), &i., '#');
		%put AESOC: &aesoc_select.;

		proc sql;
			create table aecnt_&i. as
				select usubjid, count(*) as aecnt, aebodsys
					from adae
					where aebodsys = "&aesoc_select."
					group by usubjid;
		quit;
	%end;

%mend relrisks;

updated code (did not work))

%macro relrisks();

	proc sql;
		select unique(aebodsys) into :aesocs separated by '#' from adae where aebodsys ne ' ';
		select count(unique(aesoc)) into :aen from adae where aebodsys ne ' ';
	quit;
	%let aesocs=&sqlobs2;
	%let aen=%superq(aen);
	%put &aesocs;

	/*loop through AEBODSYS values*/
	%do i=1 %to &aen;
		%let aesoc_select = %qscan(&aesocs., &i., #);
		%put AESOC: &aesoc_select.;

		proc sql;
			create table aecnt_&i. as
				select usubjid, count(*) as aecnt, aebodsys
					from adae
					where aebodsys = "&aesoc_select."
					group by usubjid;
		quit;
%end;

%mend relrisks;

[Tom]

You are still including an unneeded second query.  And worse you are running it before saving the value of SQLOBS generated by the real query so that later when you try to reference SQLOBS you get a count of 1 since the COUNT(*) selction returns only one observation.  But you are not even referencing SQLOBS macro variable, but some other macro variable named SQLOBS2 that you do not show any code to create.  Also you seem to have reversed the meaning the two variables in the %LET statements trying to put the list of terms into the ..N variable and count into the other variable.

Change:

proc sql;
  select unique(aebodsys) into :aesocs separated by '#' from adae where aebodsys ne ' ';
  select count(unique(aesoc)) into :aen from adae where aebodsys ne ' ';
quit;
%let aesocs=&sqlobs2;
%let aen=%superq(aen);
%put &aesocs;

to:

proc sql noprint;
  select unique aebodsys into :aesocs separated by '#' from adae where aebodsys ne ' ';
%let aen=&sqlobs;
%let aebodsys=%superq(aebodsys);
quit;

%put &=aen &=aebodsys;

Carefully read each of the 3 SAS statements and 3 macro statements and understand what each one is doing and how they interrelate. 

The first SAS statement is starting PROC SQL and also telling it not to write the results of a bare SELECT statement to the output window. There is no need to clutter your report with the list of values you want to put into the macro variable AEBODSYS.

The second SAS statement will generate the AEBODSYS macro variable with # delimited list of distinct values or the AEBODSYS variable in the dataset ADAE.  

The first MACRO statement will set the AEN macro variable to the value of the SQLOBS automatic macro variable that the previous SQL select statement generated.

The second MACRO statement will add macro quoting to the value of the AEBODSYS macro variable.

The third SAS statement will exit from PROC SQL.

The third MACRO statement will display the name and value of the two macro variable created.

This method might work for AEBODSYS but note that macro variables have limit of 64K bytes. Probable ok for the AEBODSYS level, but if you start trying to do something similar with individual investigator terms or even preferred terms you will quickly over flow that limit as you could easily generate a string longer than 64K bytes.