BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Patrick
Opal | Level 21

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

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

22 REPLIES 22
chrej5am
Quartz | Level 8

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
Opal | Level 21

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
Opal | Level 21

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Opal | Level 21

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

Astounding
PROC Star

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__
Jade | Level 19

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__
Jade | Level 19

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
Opal | Level 21

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__
Jade | Level 19

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__
Jade | Level 19

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

Ksharp
Super User

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

Patrick
Opal | Level 21

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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