topic Re: Splitby macro in SAS Programming

Splitby macro

jacksonan123 — Fri, 01 Nov 2019 03:15:59 GMT

I have a very simple data set newt1 and I get the following error. What name should

I have used since I want it split by one record without the error?

subj time cp

1 0 0

2 0 0

%macro splitdsnbyobs(newt1,1); 
%RecsInDS(&dsn,no_obs); /* Read more about this macro at http://sastechies.blogspot.com/2009/11/ways-to-count-number-of-obs-in-dataset.html */ 

/*calculate the final obs for each do loop iteration*/ 

%macro finalobs; 
 %if %sysfunc(ceil(%eval(&i * &splitby.))) gt &no_obs %then &no_obs; 
 %else %sysfunc(ceil(%eval(&i * &splitby.))); 
%mend finalobs; 
/* keep the observations from firstobs= and obs=*/ 
%do i=1 %to %sysfunc(ceil(&no_obs/&splitby)); 
data &dsn.&i.; 
set &dsn (firstobs=%sysfunc(floor(%eval((&i.-1)*&splitby.+1))) obs=%finalobs); 
run; 
%end; 
%mend splitdsnbyobs;

OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

ERROR: Invalid macro parameter name 1. It should be a valid SAS identifier no longer than 32 characters.

ERROR: A dummy macro will be compiled.

Re: Splitby macro

ChrisNZ — Fri, 01 Nov 2019 03:43:24 GMT

The second parameter is the name of a macro variable. 1 is not a valid name.

Re: Splitby macro

ScottBass — Fri, 01 Nov 2019 04:59:40 GMT

There are many ways to tackle this, mine is but one...

%macro splitds(data,parts);
data parts;
   if 0 then set &data nobs=nobs;
   do num=1 to &parts;
      if num=1 then do;
         firstobs=1;
         obs=ifn(num=&parts,nobs,int(nobs/&parts)*num);
         output;
      end;
      else do;
         firstobs=obs+1;
         obs=ifn(num=&parts,nobs,int(nobs/&parts)*num);
         output;
      end;
   end;
   stop;
   keep num firstobs obs;
run;

%* inner helper macro ;
%macro code;
   data dsn&num;
      set &data (firstobs=&firstobs obs=&obs);
   run;
%mend;
%loop_control(control=parts);
%mend;

%splitds(sashelp.zipcode,1)
%splitds(sashelp.zipcode,2)
%splitds(sashelp.zipcode,3)
%splitds(sashelp.zipcode,4)

%loop_control and other utility macros:

https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas

Re: Splitby macro

Shmuel — Fri, 01 Nov 2019 09:35:23 GMT

You have not posted your full code.

You defined next line with two arguments that should include variable names

%macro splitdsnbyobs(newt1,1);

suppose you want to split into 3 datasets and your dataset name is newt1

then your code should be:

%macro splitdsnbyobs(dsn,splitby); /* defined macro with arguments to be used in code */
%RecsInDS(&dsn,no_obs); /* Read more about this macro at http://sastechies.blogspot.com/2009/11/ways-to-count-number-of-obs-in-dataset.html */ 

/*calculate the final obs for each do loop iteration*/ 

%macro finalobs; 
 %if %sysfunc(ceil(%eval(&i * &splitby.))) gt &no_obs %then &no_obs; 
 %else %sysfunc(ceil(%eval(&i * &splitby.))); 
%mend finalobs; 
/* keep the observations from firstobs= and obs=*/ 
%do i=1 %to %sysfunc(ceil(&no_obs/&splitby)); 
data &dsn.&i.; 
set &dsn (firstobs=%sysfunc(floor(%eval((&i.-1)*&splitby.+1))) obs=%finalobs); 
run; 
%end; 
%mend splitdsnbyobs; 

%splitdsnbyobs(newt1,3); /* line added to execute the macro code */

Re: Splitby macro

jacksonan123 — Fri, 01 Nov 2019 11:55:19 GMT

I used your code with the following data set below (newt1) and got the
following error.

100 %splitdsnbyobs(newt1,3); /* line added to execute the macro code
*/

NOTE: Line generated by the invoked macro "SPLITDSNBYOBS".

100 %RecsInDS(&dsn,no_obs); %macro finalobs; %if
%sysfunc(ceil(%eval(&i * &splitby.))) gt &no_obs %then &no_obs;

_

180

100 ! %else %sysfunc(ceil(%eval(&i * &splitby.))); %mend finalobs;

WARNING: Apparent invocation of macro RECSINDS not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference NO_OBS not resolved.

WARNING: Apparent symbolic reference NO_OBS not resolved.

ERROR: Argument 1 to function CEIL referenced by the %SYSFUNC or %QSYSFUNC
macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC
argument list. Execution of %SYSCALL statement or %SYSFUNC

or %QSYSFUNC function reference is terminated.

ERROR: A character operand was found in the %EVAL function or %IF condition
where a numeric operand is required. The condition was:

%sysfunc(ceil(&no_obs/&splitby))

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro SPLITDSNBYOBS will stop executing.

101

DATA NEWt1;

INPUT SUBJ TRT $ AUCL AUCI CPEAK HALF TPEAK TLAG ;

CARDS;

1 A 2079.16 2087.26 2556.76 1.1 1.5 1

2 A 1377.26 . 777.38 . 8 8

3 A 1899.42 1908.64 2002.591 1.13 1.5 1

;

run;

Re: Splitby macro

Shmuel — Fri, 01 Nov 2019 12:19:24 GMT

I think that you don't need the %eval macro function under %sysfunc.

Try next code:

%macro splitdsnbyobs(dsn,splitby); /* defined macro with arguments to be used in code */
%RecsInDS(&dsn,no_obs); /* Read more about this macro at http://sastechies.blogspot.com/2009/11/ways-to-count-number-of-obs-in-dataset.html */ 

/*calculate the final obs for each do loop iteration*/ 

%macro finalobs; 
 %if %sysfunc(ceil(&i * &splitby.)) gt &no_obs %then &no_obs; 
 %else %sysfunc(ceil(&i * &splitby.)); 
%mend finalobs; 
/* keep the observations from firstobs= and obs=*/ 
%do i=1 %to %sysfunc(ceil(&no_obs/&splitby)); 
data &dsn.&i.; 
set &dsn (firstobs=%sysfunc(floor(&i.-1)*&splitby.+1) obs=%finalobs); 
run; 
%end; 
%mend splitdsnbyobs; 

%splitdsnbyobs(newt1,3); /* line added to execute the macro code */

Re: Splitby macro

jacksonan123 — Fri, 01 Nov 2019 12:31:19 GMT

I got the following log with the same errors as before with new code same
data set.

DATA NEWT1;

INPUT SUBJ TRT $ AUCL AUCI CPEAK HALF TPEAK TLAG ;

CARDS;

1 A 2079.16 2087.26 2556.76 1.1 1.5 1

2 A 1377.26 . 777.38 . 8 8

3 A 1899.42 1908.64 2002.591 1.13 1.5 1

;

RUN;

85 %macro splitdsnbyobs(dsn,splitby); /* defined macro with
arguments to be used in code */
86 %RecsInDS(&dsn,no_obs); /* Read more about this macro at
86 !
http://sastechies.blogspot.com/2009/11/ways-to-count-number-of-obs-in-datase
t.html */
87
88 /*calculate the final obs for each do loop iteration*/
89
90 %macro finalobs;
91 %if %sysfunc(ceil(&i * &splitby.)) gt &no_obs %then &no_obs;
92 %else %sysfunc(ceil(&i * &splitby.));
93 %mend finalobs;
94 /* keep the observations from firstobs= and obs=*/
95 %do i=1 %to %sysfunc(ceil(&no_obs/&splitby));
96 data &dsn.&i.;
97 set &dsn (firstobs=%sysfunc(floor(&i.-1)*&splitby.+1)
obs=%finalobs);
98 run;
99 %end;
100 %mend splitdsnbyobs;
101
102 %splitdsnbyobs(newt1,3); /* line added to execute the macro code
*/
NOTE: Line generated by the invoked macro "SPLITDSNBYOBS".
102 %RecsInDS(&dsn,no_obs); %macro finalobs; %if
%sysfunc(ceil(&i * &splitby.)) gt &no_obs %then &no_obs; %else
_
180
102 ! %sysfunc(ceil(&i * &splitby.)); %mend finalobs;
WARNING: Apparent invocation of macro RECSINDS not resolved.
MPRINT(SPLITDSNBYOBS): %RecsInDS(newt1,no_obs);

ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference NO_OBS not resolved.
WARNING: Apparent symbolic reference NO_OBS not resolved.
ERROR: Argument 1 to function CEIL referenced by the %SYSFUNC or %QSYSFUNC
macro function is not a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC
argument list. Execution of %SYSCALL statement or %SYSFUNC
or %QSYSFUNC function reference is terminated.
ERROR: A character operand was found in the %EVAL function or %IF condition
where a numeric operand is required. The condition was:
%sysfunc(ceil(&no_obs/&splitby))
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro SPLITDSNBYOBS will stop executing.
103
104 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: Splitby macro

Tom — Fri, 01 Nov 2019 12:51:28 GMT

The main issue is that mistakenly modified the DEFINITION of the macro by changing the parameter NAMES in the %MACRO statement. Instead you want to pass in the values to use when you CALL the macro.

The macro is a little silly (over complicated? confused?) because it is trying to define another macro inside the body of the macro definition. It never makes sense to define a macro inside of another macro. The name space for macros is flat, not nested. So a macro defined inside of another macro is the same as it would be if it was defined outside. You don't need that macro as you can just do the %IF statement as part of the outer macro definition. And you don't even need the %IF statement as all you doing is taking the MIN() of two numbers.

So first simplify the macro definition.

%macro splitdsnbyobs(dsn,splitby); 
%local no_obs i firstobs finalobs;
%RecsInDS(&dsn,no_obs); 

%do i=1 %to %sysfunc(ceil(&no_obs/&splitby)); 
/*calculate the first and final obs for each do loop iteration*/
  %let firstobs=%sysfunc(floor((&i.-1)*&splitby.+1));
  %let finalobs=%sysfunc(ceil(&i * &splitby.));
  %let finalobs=%sysfunc(min(&finalobs,&no_obs)); 

/* keep the observations from firstobs= and obs=*/ 
data &dsn.&i.; 
  set &dsn (firstobs=&firstobs. obs=%finalobs.); 
run; 
%end; 
%mend splitdsnbyobs;

Then try calling it.

%splitdsnbyobs(newt1,1);

Re: Splitby macro

jacksonan123 — Fri, 01 Nov 2019 12:54:19 GMT

This is the log but same error.

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73

74 DATA NEWt1;

75 INPUT SUBJ TRT $ AUCL AUCI CPEAK HALF TPEAK TLAG ;

76 CARDS;

NOTE: The data set WORK.NEWT1 has 3 observations and 8 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.02 seconds

80 ;

81 run;

82

83 %macro splitdsnbyobs(dsn,splitby);

84 %local no_obs firstobs finalobs;

85 %RecsInDS(&dsn,no_obs);

86

87 %do i=1 %to %sysfunc(ceil(&no_obs/&splitby));

88 /*calculate the first and final obs for each do loop iteration*/

89 %let firstobs=%sysfunc(floor((&i.-1)*&splitby.+1));

90 %let finalobs=%sysfunc(ceil(&i * &splitby.));

91 %let finalobs=%sysfunc(min(&finalobs,&no_obs));

92

93 /* keep the observations from firstobs= and obs=*/

94 data &dsn.&i.;

95 set &dsn (firstobs=&firstobs. obs=%finalobs.);

96 run;

97 %end;

98 %mend splitdsnbyobs;

99

100 %splitdsnbyobs(newt1,1);

NOTE: Line generated by the invoked macro "SPLITDSNBYOBS".

100 %RecsInDS(&dsn,no_obs);

_

180

WARNING: Apparent invocation of macro RECSINDS not resolved.

ERROR 180-322: Statement is not valid or it is used out of proper order.

ERROR: Argument 1 to function CEIL referenced by the %SYSFUNC or %QSYSFUNC
macro function is not a number.

ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC
argument list. Execution of %SYSCALL statement or %SYSFUNC

or %QSYSFUNC function reference is terminated.

ERROR: A character operand was found in the %EVAL function or %IF condition
where a numeric operand is required. The condition was:

%sysfunc(ceil(&no_obs/&splitby))

ERROR: The %TO value of the %DO I loop is invalid.

ERROR: The macro SPLITDSNBYOBS will stop executing.

101

102 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

114

User: sasdemo

Re: Splitby macro

Shmuel — Fri, 01 Nov 2019 15:47:00 GMT

Pay attention to next lines in the log:

WARNING: Apparent invocation of macro RECSINDS not resolved.
MPRINT(SPLITDSNBYOBS): %RecsInDS(newt1,no_obs);

ERROR 180-322: Statement is not valid or it is used out of proper order.

Macro RECSINDS - you need to copy it from the URL and include it into your code.

I accept @Tom's note and suggest to reorganize the code:

%macro RecsInDS( ....);
     ... copy macro fro the URL ...
%mend;

%macro finalobs; 
   %if %sysfunc(ceil(%eval(&i * &splitby.))) gt &no_obs %then &no_obs; 
   %else %sysfunc(ceil(%eval(&i * &splitby.))); 
%mend finalobs; 

%macro splitdsnbyobs(dsn,splitby); 
    %RecsInDS(&dsn,no_obs);
   
    %do i=1 %to %sysfunc(ceil(&no_obs/&splitby)); 
           %finalobs;
           data &dsn.&i.; 
               set &dsn (firstobs=%sysfunc(floor(%eval((&i.-1)*&splitby.+1))) 
                              obs=%finalobs); 
            run; 
     %end; 
%mend splitdsnbyobs; 

%splitdsnbyobs(newt1,3);

Pay attention, usage of a macro language is in two steps:

1) Macro definition with macro variables as arguments

%macro macro_name(arg_name, arg2_name, ...);

.... code uses &arg_name ...

%mend;

2) Macro execution with values supplied in order of arguments:

%macro_name(.....);

Re: Splitby macro

Tom — Fri, 01 Nov 2019 16:15:41 GMT

You cannot call a macro that you haven't defined. You either need to include the macro definition (or have it in your autocall library).

But you don't need that macro. Replace

%RecsInDS(&dsn,no_obs);

with

data _null_;
  call symputx('no_obs',no_obs);
  stop;
  set &dsn nobs=no_obs;
run;