Hi , I create a function sql_query_concat , maybe you can use like mysql group_concat with something different select x , y group_concat( z ) from test_monotonic group by x ,y ; /* demo data create */ data test_monotonic; length x $3. ; length y $1. ; x = "a" ; y = "b" ; z = 3 ; output ; x = "a" ; y = "b" ; z = 1 ; output ; x = "aa" ; y = "b" ; z = 3 ; output ; x = "aa" ; y = "b" ; z = 2 ; output ; x = "aaa" ; y = "b" ; z = 3 ; output ; x = "aaa" ; y = "b" ; z = 3 ; output ; run ; /* pass sql command (sql_cmd ) as parameter and concat the result with optional delimiter parameter (DLM) */ %macro sql_query_concat( sql_cmd ,DLM=%str(,), OUT=qry_result ) ; %local qry_table ; %global &OUT. ; %let sql_cmd = %qsysfunc( dequote( &sql_cmd. ) ) ; %let qry_table = sqlQuery%substr(%sysfunc(compress( %sysfunc(time())*%sysfunc(ranuni(32767))*100)) ,1,5 ) ; proc sql noprint; create table &qry_table. as %unquote( &sql_cmd. ) ; select * into : &OUT. separated by "&DLM." from &qry_table. ; drop table &qry_table. ; quit ; %mend ; /* for run_macro usage */ %macro Fsql_query_concat() ; %let sql_cmd = %qsysfunc(dequote( &sql_cmd. ) ) ; %let DLM = %qsysfunc( dequote( &DLM. ) ) ; %sql_query_concat( &sql_cmd. ,DLM=&DLM., OUT=OUTF ) %mend ; /* create function version of macro sql_query_concat */ proc fcmp outlib=work.func.tmp; function sql_query_concat( sql_cmd $ , DLM $ ) $ ; LENGTH OUTF $256 ; rc = run_macro( 'Fsql_query_concat' , sql_cmd ,DLM ,OUTF ) ; return(OUTF); endsub ; quit ; options cmplib=work.func ; /*by determined the column x and y , concat the records into a string */ proc sql ; select distinct x , y ,sql_query_concat("select z from test_monotonic where x = '"|| test_monotonic.x || "' and y = '" || test_monotonic.y || "'", ",") from test_monotonic ; quit ;
... View more
