DATA Step, Macro, Functions and more

Sort list stored as comma separated string in macro variable

Accepted Solution Solved
Reply
Respected Advisor
Posts: 4,173
Accepted Solution

Sort list stored as comma separated string in macro variable

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


Accepted Solutions
Solution
‎03-23-2015 08:58 AM
Respected Advisor
Posts: 3,799

Re: Sort list stored as comma separated string in macro variable

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

View solution in original post


All Replies
Contributor
Posts: 42

Re: Sort list stored as comma separated string in macro variable

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

Respected Advisor
Posts: 4,173

Re: Sort list stored as comma separated string in macro variable

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).

Respected Advisor
Posts: 4,173

Re: Sort list stored as comma separated string in macro variable

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;

Super User
Super User
Posts: 7,942

Re: Sort list stored as comma separated string in macro variable

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.

Respected Advisor
Posts: 4,173

Re: Sort list stored as comma separated string in macro variable

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.

Capture.PNG

Super User
Posts: 5,495

Re: Sort list stored as comma separated string in macro variable

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.

Solution
‎03-23-2015 08:58 AM
Respected Advisor
Posts: 3,799

Re: Sort list stored as comma separated string in macro variable

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
Respected Advisor
Posts: 3,799

Re: Sort list stored as comma separated string in macro variable

Posted in reply to 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

Respected Advisor
Posts: 4,173

Re: Sort list stored as comma separated string in macro variable

Posted in reply to data_null__

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.

Respected Advisor
Posts: 3,799

Re: Sort list stored as comma separated string in macro variable

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.

Respected Advisor
Posts: 3,799

Re: Sort list stored as comma separated string in macro variable

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

Super User
Posts: 10,018

Re: Sort list stored as comma separated string in macro variable

Patrick ,

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

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

Respected Advisor
Posts: 4,173

Re: Sort list stored as comma separated string in macro variable

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...).

Super User
Posts: 10,018

Re: Sort list stored as comma separated string in macro variable

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 22 replies
  • 1048 views
  • 6 likes
  • 8 in conversation