BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
capam
Pyrite | Level 9

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

 

 

 

View solution in original post

5 REPLIES 5
Tom
Super User Tom
Super User

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;

 

 

 

capam
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

@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. 

 

capam
Pyrite | Level 9
Thanks Tom. It was that simple.
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 9123 views
  • 0 likes
  • 3 in conversation