I have about 100 large datasets and within each dataset I'm hoping to extract distinct IDs to join them vertically. The datasets are unsorted, named as data\_01 , data\_02, data\_03 ....data\_100.

Since the datasets are all very large, set them together without reducing the size is not feasible, the join didn't even move after hours of running. Therefore, I believe there is the need to reduce the datasets before stacking is necessary, and I'm here to seek some help.

I tried to create a macro to select distinct ID and sum a numerical variable,cnt, by ID before vertically joining all datasets by proc sql union. The macro is not working properly:

```
/*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 also attempted to use proc summary, but there was not enough memory to run the following code:

```
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;

```

Appreciate any help!