topic Re: Union distinct records from multiple datasets in SAS Programming

Union distinct records from multiple datasets

lydiawawa — Tue, 02 Mar 2021 21:48:02 GMT

Hi,

I have about 100 large datasets and within each dataset I need to extract distinct IDs with a sum of another variable by ID, and eventually join aggregated datasets vertically.

The datasets are named as data_01 , data_02, data_03 ....data_100.

I have tried to use this before aggregation:

data combined;

set data_:;

run;

The code runs extremely slow to the point of not moving at all, which requires me to select distinct IDs and calculate sum by ID before setting them together

/*to get dataset name*/
%let mylist=;
proc sql noprint;
select catt(libname, '.', memname) into :mylist separated by ' '
from dictionary.tables where libname="mylib" and memname  like 'data_%'
;
quit;

After getting the dataset names I will need to use proc sql to union join distinct IDs with aggregated sums, such as

proc sql;
   create table new_data1 as 
      select ID, sum(cnt) from data_01
      union
      select ID, sum(cnt) from data_02
      .........
      group by ID;
quit;

How do I automate the steps to vertically combine the 100 datasets with prior conditions?

Thanks!

Re: Union distinct records from multiple datasets

lydiawawa — Tue, 02 Mar 2021 21:52:20 GMT

Typo: The name of the new table should be combined instead of new_data1

Re: Union distinct records from multiple datasets

Reeza — Tue, 02 Mar 2021 22:03:34 GMT

1. Write a macro and call it 100 times.

2. Use a view and then point proc means to the view. Quick to program, not sure about actual run time

I'd probably do #1 personally. You can use CALL EXECUTE to run it many times on the one data set or if you have a naming convention you can generate your code dynamically using macro logic. Do you have a naming convention? How do you know which data sets you need to combine? You could just generate the SQL dynamically using CALL EXECUTE as well.

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro <- suggest approach
This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

Examples of common macro usage

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

How to add data to a regular report and report on it daily

https://gist.github.com/statgeek/353374a5d8ea4f0c89ce5d80a47f4a4c

Re: Union distinct records from multiple datasets

PGStats — Tue, 02 Mar 2021 23:08:28 GMT

If your datasets are sorted (or indexed) by id you could do the aggregation by id as you read them:

data combined;
sumCnt = 0;
do until(last.id);
    set data_:(keep=id cnt);
    by id;
    sumCnt = sumCnt + cnt;
    end;
keep id sumCnt;
run;

this should run faster since you are only writing the aggregated data to the output dataset.

Re: Union distinct records from multiple datasets

lydiawawa — Wed, 03 Mar 2021 00:03:13 GMT

Unfortunately, it is not sorted...

Re: Union distinct records from multiple datasets

Reeza — Wed, 03 Mar 2021 00:19:21 GMT

proc sql;
create table data_set_list
as select catx(".", libname, memname) as dsn
from sashelp.vtables where libname="mylib" and memname like 'data_%';
quit;


%macro summarize(dsn=);

ods select none;
proc means data=&dsn SUM NWAY;
class ID;
var CNT;
ods output summary = _temp1;
run;
ods select all;

data _temp2;
set _temp1;
DSN = "&DSN";
run;

proc append base=final data=_temp2 force;
run;

proc datasets lib=work nodetails nolist;
delete _temp1 _temp2;
quit;

%mend;

data demo;
set data_set_list;

str = catt('%summarize(dsn=',
             dsn,
            ');'
           );

call execute(str);
run;

Re: Union distinct records from multiple datasets

lydiawawa — Wed, 03 Mar 2021 06:34:32 GMT

I tried to create union statements in macro, then execute it through pro sql

/*Get dataset names*/
proc sql noprint;
select  memname into :mylist separated by ' '
from dictionary.tables where libname= "mylib" and upcase(memname) like "DATA_%"
;
quit;

%put &mylist;

/*create union statements*/
%global nextdata;
%let nextdata =;
%macro combinedata(mylist);
  data _null_;
       datanum = countw("&mylist");
       call symput('Dataset', put(datanum, 10.));
  run;

   %do i = 1 %to  &Dataset ;
      data _null_;
          temp = scan("&mylist", &i);
          call symput("Dataname", strip(put(temp,$12.)));
      run;
       %put &Dataname;
       %put &Dataset;

        %if (&i=&Dataset) %then %do;
            %let nextdata = &nextdata.
                select id, sum(cnt)
                  from mylib.&&Dataname
                   group by id;
            %end;
        %else %do;
            %let nextdata = &nextdata.
               select id, sum(cnt)
                  from mylib.&&Dataname union
                   group by id;
         %end;

            %put nextdata = &nextdata;
    %end;


%mend combinedata;

%combinedata(&mylist);

/*execute from proc sql*/
proc sql;
    create table combined as (&nextdata);
quit;

I have tried my best, but for some reason the macro segments of selecting unique id and sum id is not formatted correctly for proc sql, yet I do not know how to fix the error.

Any help is appreciated.

Re: Union distinct records from multiple datasets

lydiawawa — Wed, 03 Mar 2021 00:23:05 GMT

I posted the macro I attempted to create, the min you posted this lol...

Re: Union distinct records from multiple datasets

mkeintz — Wed, 03 Mar 2021 03:52:57 GMT

Not sorted? This is what the CLASS statement in PROC SUMMARY was meant to handle.

Here's what I would suggest if you choose to run all the data sets in one process:

data vneed / view=vneed;
  set data_: (keep=id cnt);
run;
proc summary data=vneed nway;
  class id;
  var cnt;
  output out=want (drop=_type_) sum=sumcnt;
run;

Now if you are concerned about monitoring progress, you could do the above to all datasets having names starting with DATA_0 (i.e DATA_00-DATA_09), then DATA_1 (DATA_10-DATA_19 and also DATA_100), ... DATA_90-DATA_99:


options mprint;
%macro do_by_digit;
  %do d=0 %to 9;
    data vneed&d/view=vneed&d;
    set data_&d:  (keep=id cnt);
    proc summary data=vneed&d nway;
      class id;
      var cnt;
      output out=sum&d (drop=_type_) sum=cntsum;
  run;
  %end;
  data allsums;
    set sum: (rename=(_freq_=n_obs));
  run;
  proc summary data=allsums nway;
    class id;
    var cntsum n_obs;
    output out=want sum(cntsum)=cntsum sum(n_obs)=n_obs ;
  run;
%mend;
%do_by_digit;

This would produce a far less busy sas log than doing one DATA_ dataset at a time.

Re: Union distinct records from multiple datasets

lydiawawa — Wed, 03 Mar 2021 06:08:51 GMT

Thank you for the helpful response, when I tested with my datasets there is not enough memory to complete the program.

Re: Union distinct records from multiple datasets

mkeintz — Wed, 03 Mar 2021 15:36:39 GMT

@lydiawawa wrote:

Thank you for the helpful response, when I tested with my datasets there is not enough memory to complete the program.

Did you notice I provided an alternative that would do about 10 datasets at a time? Are you saying that such data segmentation still exhausted memory? If 10 is too many, you could set it up to do 5 at a time, although at some point you should probably go with @Reeza 's suggestion of 1 dataset at a time.

Re: Union distinct records from multiple datasets

Reeza — Wed, 03 Mar 2021 16:59:06 GMT

Your naming convention makes things a touch complicated by the way, if you had used Z3 or data_001 to data_100 that would have been fine. Or data_1 to data_100 but having a leading zero for 0 to 10 and then not for the 100 values is a bit inconsistent. Otherwise you could do a full macro loop very easily but this also deals with the case where the data sets do not have a naming convention.

Re: Union distinct records from multiple datasets

lydiawawa — Wed, 03 Mar 2021 17:31:14 GMT

Hi Reeza,

I do not have

sashelp.vtables

available on my SAS

I used this statement instead:

proc sql;
create table data_set_list as
 select cats('mylib.',memname) as dsn
 from dictionary.tables where libname= "mylib" and upcase(memname) like "DATA_%"
 ;

The following is the rest code:

 %macro summarize(dsn=);

ods select none;
proc means data=&dsn SUM NWAY;
class id;
var cnt;
ods output summary = _temp1;
run;
ods select all;

data _temp2;
set _temp1;
DSN = "&DSN";
run;

proc append base=final data=_temp2 force;
run;

proc datasets lib=work nodetails nolist;
delete _temp1 _temp2;
quit;

%mend;

data demo;
set data_set_list;
   str = catt('%summarize(dsn=', dsn,');');
call execute(str);
run;

The following are the first few errors I received:

ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following
       traceback information:

The SAS task name is [APPEND  ]
Segmentation Violation

Traceback of the Exception:

/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sas(+0x16a23e)
       [0x5594e557d23e]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sas(+0x4eddf) [0x5594e5461ddf]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x1
       44) [0x7f52ae353ac4]
/lib64/libpthread.so.0(+0xf630) [0x7f52af97c630]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxkern(yyrgreb+0x37)
       [0x7f52a0477a57]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxkern(yyrcgix+0x1e3)
       [0x7f52a047ae23]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxkern(yyrcgef+0xbb)
       [0x7f52a047abeb]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyh(+0x38490)
       [0x7f529b980490]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyh(+0x37797)
       [0x7f529b97f797]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyh(yyhlock+0x8e5)
       [0x7f529b97bcf5]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyoio(yyoopen+0x810f)
       [0x7f527182fa9f]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyoio(yoopen+0x1f7)
       [0x7f52718275b7]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(+0x64228)
       [0x7f5292d27228]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(ysssem+0x21c8)
       [0x7f5292d24258]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(ysprstm+0xbf8)
       [0x7f5292d2ac08]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(yspproc+0x37e)
       [0x7f5292d2b47e]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasqutil(xsparse+0x95)
       [0x7f524b5b0115]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasqutil(sasqutil+0x436)
       [0x7f524b55c146]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sas(vvtentr+0x18a)
       [0x5594e546193a]
/lib64/libpthread.so.0(+0x7ea5) [0x7f52af974ea5]
/lib64/libc.so.6(clone+0x6d) [0x7f52aef609fd]

NOTE: PROCEDURE APPEND used (Total process time):
      real time           2.50 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
2   + lib=work nodetails nolist; delete _temp1 _temp2; quit;;

Re: Union distinct records from multiple datasets

Reeza — Wed, 03 Mar 2021 17:52:43 GMT

Please re-run your code with the macro debugging options (mprint, symbolgen) and post the full log.

Re: Union distinct records from multiple datasets

lydiawawa — Wed, 03 Mar 2021 18:35:35 GMT

The full log is rather long, the code is still running. Following is a portion of mprint and symbolgen. There seems to be no problem with macro variable definition:

156  data demo;
157  set data_set_list;
158     str = catt('%summarize(dsn=', dsn,');');
159  call execute(str);
160  run;

MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_01
MPRINT(SUMMARIZE):   ods select none;
NOTE: The macro generated output from MPRINT will also be written to external
      file /home/s/shia0001/tempout while OPTIONS MPRINT and MFILE are set.
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_01
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_01 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_01
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_01";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_02
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_02
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_02 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_02
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_02";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_04
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_04
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_04 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_04
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_04";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_05
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_05
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_05 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_05
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_05";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_06
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_06
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_06 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_06
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_06";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_08
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_08
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_08 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_08
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_08";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_09
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_09
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_09 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_09
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_09";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_10
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_10
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_10 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_10
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_10";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_11
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_11
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_11 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_11
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_11";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_12
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_12
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_12 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_12
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_12";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_13
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_13
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_13 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_13
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_13";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_15
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_15
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_15 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_15
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_15";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc datasets lib=work nodetails nolist;
MPRINT(SUMMARIZE):   delete _temp1 _temp2;
MPRINT(SUMMARIZE):   quit;
MLOGIC(SUMMARIZE):  Ending execution.
MLOGIC(SUMMARIZE):  Beginning execution.
MLOGIC(SUMMARIZE):  Parameter DSN has value mylib.DATA_16
MPRINT(SUMMARIZE):   ods select none;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_16
MPRINT(SUMMARIZE):   proc means data=mylib.DATA_16 SUM NWAY;
MPRINT(SUMMARIZE):   class id;
MPRINT(SUMMARIZE):   var cnt;
MPRINT(SUMMARIZE):   ods output summary = _temp1;
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   ods select all;
MPRINT(SUMMARIZE):   data _temp2;
MPRINT(SUMMARIZE):   set _temp1;
SYMBOLGEN:  Macro variable DSN resolves to mylib.DATA_16
MPRINT(SUMMARIZE):   DSN = "mylib.DATA_16";
MPRINT(SUMMARIZE):   run;
MPRINT(SUMMARIZE):   proc append base=final data=_temp2 force;
MPRINT(SUMMARIZE):   run;

This is the error for every iteration:

NOTE: There were 52 observations read from the data set WORK.DATA_SET_LIST.
NOTE: The data set WORK.DEMO has 52 observations and 2 variables.
NOTE: Compressing data set WORK.DEMO increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: CALL EXECUTE generated line.
1   + ods select none; proc means data=mylib.DATA_01 SUM NWAY; class
id; var cnt; ods output summary = _temp1; run; ods select all; data _temp2;
set _temp1; DSN = "mylib.DATA_01"; run; proc append base=final
data=_temp2 force; run; proc datasets
NOTE: Data file mylib.DATA_01.DATA is in a format that is native to
      another host, or the file encoding does not match the session encoding.
      Cross Environment Data Access will be used, which might require
      additional CPU resources and might reduce performance.
NOTE: The data set WORK._TEMP1 has 2056463 observations and 3 variables.
NOTE: There were 6731208 observations read from the data set
      mylib.DATA_01.
NOTE: PROCEDURE MEANS used (Total process time):
      real time           2:30.77
      cpu time            1:38.38


NOTE: There were 2056463 observations read from the data set WORK._TEMP1.
NOTE: The data set WORK._TEMP2 has 2056463 observations and 4 variables.
NOTE: Compressing data set WORK._TEMP2 increased size by 17.25 percent.
      Compressed is 1774 pages; un-compressed would require 1513 pages.
NOTE: DATA statement used (Total process time):
      real time           14.45 seconds
      cpu time            0.82 seconds



ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following
       traceback information:

The SAS task name is [APPEND  ]
Segmentation Violation

Traceback of the Exception:

/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sas(+0x16a23e)
       [0x5594e557d23e]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sas(+0x4eddf) [0x5594e5461ddf]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x1
       44) [0x7f52ae353ac4]
/lib64/libpthread.so.0(+0xf630) [0x7f52af97c630]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxkern(yyrgreb+0x37)
       [0x7f52a0477a57]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxkern(yyrcgix+0x1e3)
       [0x7f52a047ae23]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxkern(yyrcgef+0xbb)
       [0x7f52a047abeb]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyh(+0x38490)
       [0x7f529b980490]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyh(+0x37797)
       [0x7f529b97f797]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyh(yyhlock+0x8e5)
       [0x7f529b97bcf5]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyoio(yyoopen+0x810f)
       [0x7f527182fa9f]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasyoio(yoopen+0x1f7)
       [0x7f52718275b7]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(+0x64228)
       [0x7f5292d27228]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(ysssem+0x21c8)
       [0x7f5292d24258]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(ysprstm+0xbf8)
       [0x7f5292d2ac08]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasxshel(yspproc+0x37e)
       [0x7f5292d2b47e]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasqutil(xsparse+0x95)
       [0x7f524b5b0115]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sasqutil(sasqutil+0x436)
       [0x7f524b55c146]
/apps/SAS/v9.4/SASHome/SASFoundation/9.4/sasexe/sas(vvtentr+0x18a)
       [0x5594e546193a]
/lib64/libpthread.so.0(+0x7ea5) [0x7f52af974ea5]
/lib64/libc.so.6(clone+0x6d) [0x7f52aef609fd]

Re: Union distinct records from multiple datasets

Reeza — Wed, 03 Mar 2021 18:50:21 GMT

Make sure you're deleting the final data set between run iterations.
I don't see what's generating that error, does the FINAL data set have what you want?

Another approach would be to to create 100's of summary data sets and just stack them all at the end but I usually don't like leaving datasets around like that personally, I find it messy. If you'd like to try that approach, remove the APPEND and PROC DATASETS, and give each output a unique name, same prefix, in the last data step. Then append them all together using the colon shortcut method.