MACRO DO LOOP, parsing a string with a given deliminator

[kjohnsonm]

Hello All,

So I want to parse a string I hand off to a macro into keys for proc SQL to do some duplicate checking can someone give me some pointers?

I have a counter to control the do loop but if there is an easer way I am game.

have this code:

libname my_lib odbc dsn=my_db schema = dbo;

%macro count(list=);
 %eval(%sysfunc(count(%cmpres(&list),%str(|)))+1)
%mend count;

%macro dups_check(file, key);
%let My_count= %count(list=&key);
%put &My_count. ;
do i=1 to &My_count.;
	%let key&i. = %scan(key, &i., '|')
	%put key&i.
end;


/* more proc sql here to find dups etc */

/* I want to be able to use the parsed keys here &key1 - &keyN  in my proc sql automated code */

%mend dups_check;

/* The above code should be able to handle any kind of variation like these items below I want to execute */

%dups_check(my_lib.my_file0,key1|key2|key3);
%dups_check(my_lib.my_file1,key1|key2);
/*...*/
%dups_check(my_lib.my_fileN,key1|key2|key3|key4|key5|...|keyN);

/* I know there are limits to string length that the memory will handle but I am not getting over 7-8 keys so I think I am safe in all cases */

[kjohnsonm]

oops and should handle a single key too.

[ballardw]

You may want to use COUNTW instead of count. Also for a large number of things the macro processor strips out leading and trailing spaces.

%macro dummy(list=);

%do i= 1 %to %sysfunc(countw(&list.,|));
   %let word= %scan(&list,&i.,|);
   %put Word &i. is &word.;
%end;
%mend;

%dummy(list= This |is|    varying  |numbers | of delimited | words);

If you add another character after the &word. in the %put such as &word.^ you will see there are no trailing spaces.

[Tom · Accepted solution]

We can clean up your code a little using newer SAS functionality to call the COUNTW() function to get the number of keys.  

%macro dups_check(file, key);
%local My_count i ;

%let My_count= %sysfunc(countw(&key,|));
%do i=1 %to &My_count;
  %local key&i ;
  %let key&i = %scan(&key, &i, |);
%end;

....
%mend dups_check;

Note that your COUNT() macro is not really needed but if you want to keep it you should upgrade it to use COUNTW() and allow caller to specify the delimiter(s).

%macro count(list=,dlm=|);
%sysfunc(countw(%superq(list),&dlm))
%mend count;

[kjohnsonm]

Thank you both, I took a little from both of your samples. I think the second code sample fits my needs the best. I need to keep each macro var key for use in my proc sql later so this works the best for my needs. -KJ

%macro dups_check(Mfile, Mkey);
%local My_count i ;
%let My_count= %sysfunc(countw(&Mkey,|));
%put &My_count.;
%do i=1 %to &My_count;
  %local Mkey&i ;
  %let Mkey&i = %scan(&Mkey, &i, |);
%end;
/*I don't like this one much if 5 or less keys I get warnings */
%put &Mkey1 &Mkey2 &Mkey3 &Mkey4 &Mkey5 &Mkey6;
%put &Mkey : ;

/* over kill, too verbose */
/*%put _ALL_;*/

/*my Proc SQL here ...*/

%mend dups_check;

/*%dups_check(file, key1_word|key2_word_is|key3_word_varying|key_4_word_numbers|key_5_of delimited|key6_words);*/

%dups_check(file, key1_word|key2_word_is|key3_word_varying|key_4_word_numbers);

%dups_check(file, key1_word|key2_word_is|);
%dups_check(file, key1_word|key2_word_is);

[Tom]

/*I don't like this one much if 5 or less keys I get warnings */
%put &Mkey1 &Mkey2 &Mkey3 &Mkey4 &Mkey5 &Mkey6;
%put &Mkey : ;

Not sure why you are putting them to the log, but if you want to let that %PUT statement run without error then make sure that at least those 6 macro variables exist.  Add them to your first %LOCAL statement.

%local My_count i Mkey1 Mkey2 Mkey3 Mkey4 Mkey5 Mkey6;

[kjohnsonm]

I was checking the status on the fly... I thought something like the maro var pattern &Mkey: ; would display what is in memory for items1 - - n, but it doesn't. It was just an assumption it would work since these two commands are valid.

proc datasets lib=work;
delete
MY_ipeds_ethnic_group_NULL_SET
MY_ipeds_ethnic_group_NULL_SET1
MY_NULL_XD_SCHOOL
;RUN;quit;

proc datasets lib=work;
delete MY_: ;
RUN;quit;

I only dislike the fact I cannot seem to find a one line-er to display my results for testing. sorry if I confused you. thank you the code works just fine as it is, just me OCD-ing.

[Tom]

%put _local_;

[kjohnsonm]

I have seen them, _all_, _local_, _global_, _AUTOMATIC_ ...none of them are quite the same as a string pattern match. thanks for the pointers. Local is very close though...