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

Sort list stored as comma separated string in macro variable

Patrick — Sun, 22 Mar 2015 22:26:49 GMT

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

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

chrej5am — Mon, 23 Mar 2015 09:33:23 GMT

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;

put str1;

stop;

run;

Good luck,

Jakub

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

Patrick — Mon, 23 Mar 2015 10:07:50 GMT

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

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

Patrick — Mon, 23 Mar 2015 10:09:44 GMT

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;

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

RW9 — Mon, 23 Mar 2015 11:54:32 GMT

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.

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

Astounding — Mon, 23 Mar 2015 12:10:03 GMT

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.

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

data_null__ — Mon, 23 Mar 2015 12:58:03 GMT

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

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

data_null__ — Mon, 23 Mar 2015 13:03:05 GMT

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

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

data_null__ — Mon, 23 Mar 2015 13:06:50 GMT

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

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

Ksharp — Mon, 23 Mar 2015 13:39:45 GMT

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

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

Ksharp — Mon, 23 Mar 2015 14:39:47 GMT

Combine it into data step :

%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) ;

%global sorted_string;

%let sorted_string=;

%do i=1 %to &n;

%let sorted_string= &sorted_string.%bquote(&&a&i) ;

%end;

%put sorted_string: %bquote(&sorted_string) ;

put(md5(symget('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;

Xia Keshan

Message was edited by: xia keshan

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

data_null__ — Mon, 23 Mar 2015 14:52:52 GMT

Would it be a good idea to add %LOCAL to insure those variables you're creating don't escape and cause havoc? Would know to do that?

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

Tom — Mon, 23 Mar 2015 16:41:04 GMT

So combining the ideas posted is sounds like you want a utility macro that will take as input a list of variable names and return the same names in sorted order.

%macro sort_names /parmbuff;

%local i n names result sep;

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

%do i=1 %to &n;

%local v&i;

%let v&i=%upcase(%scan(&syspbuff,&i,(, )));

%let names=&names.&sep.v&i;

%let sep=,;

%end;

%syscall sortc(&names);

%let sep=;

%do i=1 %to &n;

%let result=&result.&sep.&&v&i;

%let sep=,;

%end;

&result

%mend ;

%put %sort_names(a,B,C);

%put %sort_names(x1, x2 ,B,C);

You could then use that as input to CATX() or other functions to generate you unique string.

data _null_;

set sashelp.class;

key1=catx('|',%sort_names(age,'x',height));

key2=catx('|',%sort_names(height,age,'x'));

put key1= / key2= ;

stop;

run;

key1=X|14|69

key2=X|14|69

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

slchen — Mon, 23 Mar 2015 19:21:47 GMT

This is also one of methods.

%macro sort_names/parmbuff;

filename clip clipbrd;

data _null_;

file clip dlm=',';

put "&syspbuff";

data vars;

infile clip dlm=',';

input name $ @@;

name=compress(name,'()');

run;

proc sql;

select name into:result separated by ',' from vars order by name;

quit;

%let result=(&result);

%mend;

%put %sort_names(x,a,d);

%put %sort_names(x1, x2 ,B,C);

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

Patrick — Mon, 23 Mar 2015 21:16:51 GMT

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.

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

Patrick — Mon, 23 Mar 2015 21:26:06 GMT

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.

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

Patrick — Mon, 23 Mar 2015 21:28:51 GMT

Yes, he would.

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

data_null__ — Mon, 23 Mar 2015 21:30:43 GMT

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.

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

data_null__ — Mon, 23 Mar 2015 21:37:01 GMT

Yes I see, I've looked back at your original post and see that you did include %LOCAL. I don't think you would need to use _ as first character unless you just like that. I usually save the underscores for data steps where I want to use names that I am reasonably sure won't exist in the users data.

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

Patrick — Mon, 23 Mar 2015 21:40:13 GMT

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