Sort list stored as comma separated string in macro variable

[Patrick]

Hi All

I'm looking for a way to sort a comma separated list stored in a macro variable. The sort needs to run as part of a "function style" macro. SAS version is 9.4

Have:

%let var=(xx, aa, b);

Want:

%put var;

(aa,b,xx)

I've created a macro which creates a 32 character hex string using md5(). The purpose of this macro is to generate a primary key using a composite key as input.

I want that the generated key doesn't depend on the order in which the variable names get passed to the macro - though I need to sort the string with variable names before feeding it into the md5() function. It doesn't really matter how the string gets sorted as long as the algorithm returns always the same sort order.

Here how the real code looks like so far:

%macro eia_md5hex32/parmbuff;

  %local _i _varname;

  %let _i=1;

  %let _varname=%scan(&syspbuff,&_i);

  %str(put%(md5%(catq%(' ')

  %do %while(&_varname ne);

    %str(,)&_varname

    %let _i=%eval(&_i+1);

    %let _varname=%scan(&syspbuff,&_i);

  %end;

  %str(%)%), $hex32.%))

%mend ;

data _null_;

  set sashelp.class;

  key1=%eia_md5hex32(age,'x',height);

  key2=%eia_md5hex32(height,'x',age);

  put key1=;

  put key2=;

  stop;

run;

What I need is a sort which returns the same value for "key1" and "key2".

Any ideas of how to solve this or a full solution would be highly appreciated.

Thanks

Patrick

[chrej5am]

Hi,

I would suggest to sort the string before inputing it into the macro. For example you could use array sort function.

data _null_;

  set sashelp.class;

array temp(3) $ _temporary_;

str1=strip(put(age,best32.))||',x,'||strip(put(height,best32.));

put str1;

do i = 1 to dim(temp);

   temp{i}=scan(str1,i,',');

   end;

call sortc(of temp{*});

do i = 1 to dim(temp);

if i=1 then str1=temp{1};

  else do;

  str1=strip(str1)||','||strip(temp{i});

  end;

end;

put str1;

  stop;

run;

Good luck,

Jakub

[Patrick]

Thank you but I need everything contained within a single function style macro. I need to be able to use the macro as if it were a SAS function (if you know SAS DI Studio: I want to use this macro in a DI expression).

I've spent a bit more time working on a solution and I believe I was able to come up with some working code (I'll post it below). Not 100% happy with it and it would be great if somebody would have an idea of how to make this code simpler (as someone after me will have to understand and maintain it).

[Patrick]

Below the code I was so far able to come up with. I believe it works but if there is a simpler solution then I would still be very interested to get it.

%macro eia_md5hex32/parmbuff;

  %local _i sorted_string;

  /* remove brackets from &syspbuff */

  %let syspbuff=%upcase(%sysfunc(prxchange(s/\(|\)//o,-1,%bquote(&syspbuff))));

  /* sort words from &syspbuff and store result in &sorted_string */

  %do %while(%scan(%bquote(&syspbuff),1,%bquote(,)) ne );

    %let _min=%scan(%bquote(&syspbuff),1,%bquote(,));

    %do _i=2 %to %sysfunc(countw(%bquote(&syspbuff),%bquote(,)));

      %let _word=%scan(%bquote(&syspbuff),&_i,%bquote(,));

      %if %bquote(&_min) > %bquote(&_word) %then %let _min=&_word;

    %end;

    %let sorted_string=&sorted_string cats(&_min);

    %let syspbuff=%sysfunc(prxchange(s/&_min%str(,)?//o,-1,%bquote(&syspbuff)));

  %end;

  /* separate sorted words with commas */

  %let sorted_string=%sysfunc(prxchange(s/ +/%str(,)/o,-1,%bquote(&sorted_string)));

  /* write SAS function creating md5() 32 character hex string */

  put(md5(catq(' ',&sorted_string)), $hex32.)

%mend ;

data _null_;

  set sashelp.class;

  key1=%eia_md5hex32(age,'x',height);

  key2=%eia_md5hex32(height,'x',age);

  put key1=;

  put key2=;

  stop;

run;

[RW9]

Hi,

Sorry, have to agree with Jakub Chrenko here.  Your trying to write base SAS code in macro language.  It will cause you headaches if you go there, parmbuff could contain anything would be the first problem I see.  Also, not seeing what benefit you gain from this function:

data test;

  length var1-var3 $10.;

  var1="zzz";

  var2="aa";

  var3="bb";

  array var{*} var1-var3;

  call sortc(of var{*});

  tmp=md5(catx(',',of var{*}));

run;

The above uses the call sortc() function to re-arrange the var{*} into order,md5 is then called with the sorted array.

[Patrick]

As already replied to Jakub: I need it fully function style as I want to use this macro like a SAS function in a SAS DI Studio Expression.

[Astounding]

Would it help to sort earlier, before adding commas and parentheses?  That type of sorting (inefficient though it may be) has been around for a long time ... compare word1 with word2 and if the order is wrong, switch them.  Continue until there are no further changes to make.  If it would be useful, I can sketch it out.

[data_null__ · Accepted solution]

Why not just call SORTC function with %SYSCALL.  You will have to put the comma separated listing into macro variables first but that won't be a problem.

%let a=5.6; %let b=x; %let c=4.3;
%put NOTE: &=a &=b &=c;
%syscall sortc(a,b,c);
%put NOTE: &=a &=b &=c;


32         %let a=5.6; %let b=x; %let c=4.3;
33         %put NOTE: &=a &=b &=c;
NOTE: A=5.6 B=x C=4.3
34         %syscall sortc(a,b,c);
35         %put NOTE: &=a &=b &=c;
NOTE: A=4.3 B=5.6 C=x

[data_null__]

Or if the values are all numeric.

%let a=5.16; %let b=10.01; %let c=4.3;
%put NOTE: &=a &=b &=c;
%syscall sortn(a,b,c);
%put NOTE: &=a &=b &=c;

32         %let a=5.16; %let b=10.01; %let c=4.3;
33         %put NOTE: &=a &=b &=c;
NOTE: A=5.16 B=10.01 C=4.3
34         %syscall sortn(a,b,c);
35         %put NOTE: &=a &=b &=c;
NOTE: A=4.3 B=5.16 C=10.01

[Patrick]

Yes, I believe this is it. I've tried using sortc() but didn't realize that I need to reference the variables in the function without ampersand.

[data_null__]

The "thing" with call routines is that the alter the values of variables, in this case macro variables.  I usually forget and try to use constants too or include the & with the macro variable, causes all sorts of problems.

[data_null__]

Also remember you can also DOSUBL a bit of SAS PROC and/or DATA STEP code and still stay function style macro.

[Ksharp]

Patrick ,

I remember every question you posted here is not really easy . I know how good you were .

Maybe next time you should call SAS support firstly ? You know where they are .

%macro eia_md5hex32/parmbuff;

%let list=;

%let n=%sysfunc(countw( %bquote(&syspbuff) , %str(,) ));

%do i=1 %to &n;

  %let a&i= %qscan(%bquote(&syspbuff),&i,%str(,%(%)) );

  %let list= &list.,a&i ;

%end;

  %let list=%substr(%bquote(&list),2);

  %syscall sortc(

   &list

  );

  %put  &list  %bquote(&a1 &a2 &a3) ;

%mend;

%eia_md5hex32(age,'x',height)

171

172  %eia_md5hex32(age,'x',height)

a1,a2,a3  'x' age height

Xia Keshan

[Patrick]

Thank you very much for your solution. That's very helpful.

Why should I throw such a question at SAS TechSupport. There is a bunch of very capable people here in these forums - and these forums are here to ask others for help and to help others.

SAS TechSupport's role is imho primarily for support with technical issues and not for programming/usage questions (the lines are blurred, but still...).

[Ksharp]

I just want know how good those sas guys were .  maybe they were as good as you are .