topic Re: Generate multiple tables with different names in SAS Programming

Generate multiple tables with different names

capam — Tue, 25 Jul 2017 13:29:05 GMT

The code below illustrates the concept of creating subtables from a larger tables based on vehicle numbers. I'm not sure of the syntax in 'create table'.

%macro tableLoop;
proc sql;
array VehNo[3] _temporary_ (3725:3727)
do _n_ = 1 to 3;
create table DWM_&VehNo[_n_]_Min_list as
select t1.*
FROM DWM_Min_list t1
WHERE t1.VEHICLE_NO = VehNo[_n_];
end;
/*
array VehNo[60] _temporary_ (3725:3763 6502 6505 6506 6854 6860 
6924 6928 8029 8037 8057 8098 8106 8125 8132 8145) 
*/
quit;
%mend;

Re: Generate multiple tables with different names

Tom — Tue, 25 Jul 2017 14:10:53 GMT

In general it is probably a bad idea to split your nice dataset into lots of little datasets.

But here are some concepts that might help.

1) You can generate multiple datasets in one data step. That could save a lot of I/O time since you will only need to read the input dataset once. Something like this:

data dwm_6502 dwm_6505 dwm_ 6506;
 set dwm_all ;
 if vehicle_no = 6502 then output dwm_6502;
 else if vehicle_no = 6505 then output dwm_6505;
 else if vehicle_no = 6506 then output dwm_6506;
run;

2) If you want to use concepts like ARRAYs and numreic ranges specified by low:high syntax then you will need to use a data step instead of macro code. But for this type of data driven code generation it is easier to use a data step to generate the code than macro code to generate the code anyway. So you might try something like this to write many separate data steps (or create table statements if you prefer).

file code temp;
data _null_;
  file code;
  array VehNo[200] _temporary_ 
  (3725:3763 6502 6505 6506 6854 6860 6924 6928 8029 8037 8057 8098 8106 8125 8132 8145) 
  ;
  do i=1 to dim(VehNo) while (VehNo(i) ne .) ;
     num=VehNo(i);
     put 
/ 'create table DWM_' num +(-1) '_Min_list as'
/ '  select t1.*'
/ '  from DWM_Min_list t1'
/ '  WHERE t1.VEHICLE_NO = ' num 
/ ';'
    ;
  end;
run;

proc sql;
%include code / source2;
quit;

3) Another way is to create a macro to process one Vehicle number. Then use the data step to generate the macro calls.

%macro split(num);
create table DWM_&num._Min_list as
  select t1.*
  from DWM_Min_list t1
  WHERE t1.VEHICLE_NO = &num 
;
%mend split;

data _null_;
  array VehNo[200] _temporary_ 
  (3725:3763 6502 6505 6506 6854 6860 6924 6928 8029 8037 8057 8098 8106 8125 8132 8145) 
  ;
  call execute('proc sql;');
  do i=1 to dim(VehNo) while (VehNo(i) ne .) ;
     call execute(cats('%nrstr(%split)(',VehNo(i),')'));
  end;
  call execute('quit;');
run;

Re: Generate multiple tables with different names

ballardw — Tue, 25 Jul 2017 14:15:29 GMT

Neither Array nor DO loop are allowed in Proc SQL.

The create table would not accept a data set type variable in creating the name for the table. A MACRO loop %do would though the structure would have to be somewhat different.

If you are attempting to create a data set for each value of a given variable please describe why. In most cases any processing along those lines works much better with BY group processing.

Before you started to write this macro did you have any working base SAS code? That is generally a requirement before writing a macro to run a loop or parameterized version of code.

Re: Generate multiple tables with different names

capam — Tue, 25 Jul 2017 14:33:00 GMT

Hi Tom,

Thanks for the quick response. I tried your 3rd suggestion and got the following error message. Should there be a format declaration?

1 + proc sql;
2 + %split(3725)
MLOGIC(SPLIT): Beginning execution.
MLOGIC(SPLIT): Parameter NUM has value 3725
MPRINT(SPLIT): create table DWM_3725_Min_list as select t1.* from DWM_Min_list t1 WHERE t1.VEHICLE_NO = 3725 ;
ERROR: Expression using equals (=) has components that are of different data types.

Re: Generate multiple tables with different names

Tom — Tue, 25 Jul 2017 14:44:57 GMT

@capam wrote:

Hi Tom,

Thanks for the quick response. I tried your 3rd suggestion and got the following error message. Should there be a format declaration?

1 + proc sql;
2 + %split(3725)
MLOGIC(SPLIT): Beginning execution.
MLOGIC(SPLIT): Parameter NUM has value 3725
MPRINT(SPLIT): create table DWM_3725_Min_list as select t1.* from DWM_Min_list t1 WHERE t1.VEHICLE_NO = 3725 ;
ERROR: Expression using equals (=) has components that are of different data types.

You need to start with knowing what code you want to generate before you can really get any code generation project to work.

It seems clear from that error message that VEHICLE_NO is not a number. It is a string. It might be as simple as changing the where clause to add quotes around the value so that you are generating a string literal instead of numeric literal.

WHERE t1.VEHICLE_NO = "&num"

but you would need to know how the character strings are actually stored to know if that will work.

Re: Generate multiple tables with different names

capam — Tue, 25 Jul 2017 14:51:36 GMT

Thanks Tom. It was that simple.