topic Re: create a list of SAS tables with the same prefix in SAS Programming

create a list of SAS tables with the same prefix

afsand — Sun, 05 Jun 2022 15:55:42 GMT

Hi Everyone,

I want to create a list of SAS tables with the same prefix. I am not sure how to do it or if it is possible, but I need to do it with a data step.

The table names will look like this: results_group1, results_group2,.....results_group50.

Thanks

Re: create a list of SAS tables with the same prefix

PaigeMiller — Sun, 05 Jun 2022 16:04:01 GMT

@afsand wrote:

I want to create a list of SAS tables with the same prefix. I am not sure how to do it or if it is possible, but I need to do it with a data step.

Why restrict solutions to just one method? PROC SQL works as well.

data want;
    set sashelp.vtable(where=(memname=:'RESULT' and libname='WORK'));
    keep memname;
run;

proc sql;
    create table want as select memname from sashelp.vtable 
    where memname eqt 'RESULT' and libname='WORK';
quit;

Re: create a list of SAS tables with the same prefix

afsand — Sun, 05 Jun 2022 16:14:08 GMT

Actually I want to create a list of tables not variables. I need to use only the data step, but I read information from a data step and do some calculations and want to save the results in a series of tables by groupe.

like this:

data results_group1-results_group50;
set adherants ;

some calculations in between ;
save results in the group tables;

run;

Re: create a list of SAS tables with the same prefix

Kurt_Bremser — Sun, 05 Jun 2022 16:37:12 GMT

Keep everything in one table, create a group variable, and use BY group processing in whatever you do next.

Re: create a list of SAS tables with the same prefix

afsand — Sun, 05 Jun 2022 18:12:23 GMT

Hi Kurt,

The group by statement will create a table by group? or it will just group the table?
I need to have a table for each group.

Re: create a list of SAS tables with the same prefix

PaigeMiller — Sun, 05 Jun 2022 19:48:18 GMT

@afsand wrote:
Actually I want to create a list of tables not variables.

The code I gave creates a list of tables, not variables. Exactly what you asked for.

data results_group1-results_group50;
set adherants ;

some calculations in between ;
save results in the group tables;

run;

As stated by @Kurt_Bremser , this is not a good idea, keep everything in one data set, and use an indicator or group variable to determine which group it belongs to. Don't try to split the data. Use it as one big data set, you can pull out selected parts as you need them, by using a WHERE statement, or use BY processing to handle all groups.

Re: create a list of SAS tables with the same prefix

Tom — Sun, 05 Jun 2022 20:23:27 GMT

Please explain WHY you want to create multiple datasets?

To generate code like that you need to use code generation. Either via the SAS Macro Language or by just writing the code to a text file using a data step.

For example if you wanted to generate a data statement that names 50 output datasets you could use code like this to write that to a file.

filename code temp;
data _null_;
  file code lrecl=70 ;
  put 'data ' @ ;
  do group=1 to 50;
     put 'results_group' group @;
  end;
  put ';' ;
run;

Now that you have that statement in a file you could include into your program using a %INCLUDE statement.

%include code / source2; 
  set adherants ;
/*
some calculations in between ;
save results in the group tables;
*/
run;

Note that to actually save the results to multiple dataset will also probably involve some type of code generation. Unless you want the same thing written to all 50 datasets.

Example:

5    data class0-class1 ;
                -
                22
                200
ERROR 22-322: Syntax error, expecting one of the following: a name,
              a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

6      set sashelp.class;
7      select (sex='M');
8        when (0) output class0;
9        when (1) output class1;
10     end;
11   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.CLASS0 may be incomplete.  When this step was
         stopped there were 0 observations and 5 variables.
WARNING: The data set WORK.CLASS1 may be incomplete.  When this step was
         stopped there were 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


12   data class0 class1 ;
13     set sashelp.class;
14     select (sex='M');
15       when (0) output class0;
16       when (1) output class1;
17     end;
18   run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS0 has 9 observations and 5 variables.
NOTE: The data set WORK.CLASS1 has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Re: create a list of SAS tables with the same prefix

Patrick — Sun, 05 Jun 2022 23:01:27 GMT

@afsand wrote:
I need to have a table for each group.

That's normally sub-optimal design done by SAS users who haven't fully understood yet the power of by-group processing.

Please tell us WHY you need separate tables and why you can't just have a single table with a variable that stores the group.

Re: create a list of SAS tables with the same prefix

Kurt_Bremser — Mon, 06 Jun 2022 06:14:47 GMT

@afsand wrote:

I need to have a table for each group.

No you don't. At least in 99% of cases which we see here. Explain WHY you think you need separate datasets. Tell us about the further processing you intend to do.