topic Re: How to list all the datasets with same end in a simpler way? in SAS Programming

How to list all the datasets with same end in a simpler way?

Robin_moon — Fri, 26 Apr 2024 06:58:35 GMT

I saw a loop code the other day. And it generate files of output_&i (i.e. output_1, output_2,..., output_n). At the end, it row bind all the files as:

data all_results;
     set output_:;
run;

It seems that we could list files output_1, output_2, ... , output_n in a simple way by using ":" as output_:, so I tried to row bind my files that all end up with "_output" (i.e. 1_output, 2_output, 3_output) in a similar way:

data all_results;
    set :_output;
run;

But it failed. I'd like to know how to write the code in the right way. Thanks!

Re: How to list all the datasets with same end in a simpler way?

Kurt_Bremser — Fri, 26 Apr 2024 07:32:41 GMT

The : wildcard works only for variable parts at the end of a dataset name.

proc sql noprint;
select memname into :datasets separated by " "
from dictionary.tables
where libname = "WORK" and memname like '%_OUTPUT';
quit;

data all_results;
set &datasets.;
run;

Re: How to list all the datasets with same end in a simpler way?

Tom — Fri, 26 Apr 2024 14:21:24 GMT

The : wildcard only works at the end.

The simplest solution is to change your naming pattern to use a common prefix instead.

Otherwise you will have to build your own logic to make the list. If the list is small enough (less than 64K bytes) then you can insert it into a macro variable.

proc sql noprint;
%let dslist=;
select nliteral(memname) into :dslist separated by ' '
from dictionary.members
where libname='WORK'
  and memtype in ('DATA','VIEW')
  and memname like '%COUNT'
;
quit;

data countall;
  set &dslist;
run;

Note that if you are using the numeric suffix then you can a range of values instead. That way you could reference COUNT1 to COUNT5 and also ignore COUNTALL.

data countall;
  set count1-count5;
run;

Re: How to list all the datasets with same end in a simpler way?

Reeza — Fri, 26 Apr 2024 15:07:24 GMT

Here is a reference that illustrates how to refer to variables and datasets in a short cut list:
https://blogs.sas.com/content/iml/2018/05/29/6-easy-ways-to-specify-a-list-of-variables-in-sas.html

The method you're looking for unfortunately doesn't easily exist. The are workarounds (see @Tom 's answer) and other methods you can use are in the post above.

@Robin_moon wrote:

I saw a loop code the other day. And it generate files of output_&i (i.e. output_1, output_2,..., output_n). At the end, it row bind all the files as:
data all_results;
     set output_:;
run;
It seems that we could list files output_1, output_2, ... , output_n in a simple way by using ":" as output_:, so I tried to row bind my files that all end up with "_output" (i.e. 1_output, 2_output, 3_output) in a similar way:
data all_results;
    set :_output;
run;
But it failed. I'd like to know how to write the code in the right way. Thanks!

Re: How to list all the datasets with same end in a simpler way?

Robin_moon — Sun, 28 Apr 2024 08:57:40 GMT

Thanks for sharing the link. So sad to hear from that. SAS has a shortcut for selecting variables with same prefix but not for suffix.