topic Re: Macro Code for splitting data-set and running Proc Reg on each data-set in SAS Programming

Macro Code for splitting data-set and running Proc Reg on each data-set

butter30 — Wed, 17 Dec 2014 10:28:32 GMT

Hi all

I was wondering if someone can help me create the following macro. I am given dataset call it "dat" with 100,000 observations with variables a b c d e, where b and c are categorical variables.

1) I want to split the data into groups each containing 5000 observations.

2) Run proc reg on each individually created dataset.

3) Create a table containing all of the parameter estimates.

This is how far I have managed to get;

%macro split(ndat=20);

data %do i = 1 %to &ndat.; dat&i. %end; ;

retain x;

set dat nobs=nobs;

if _n_ eq 1

then do;

if mod(nobs,&ndat.) eq 0

then x=int(nobs/&ndat.);

else x=int(nobs/&ndat.)+1;

end;

if _n_ le x then output dat1;

%do i = 2 %to &ndat.;

else if _n_ le (&i.*x)

then output dat&i.;

%end;

run;

%mend split;

%split(ndat=20) ;

/*creates 20 dat-sets I want to somehow implete the code below for each dataset*/

%macro test(data=);

ods output ParameterEstimates=parm;

/*this creates parameter estimates I want to combine into one table containing all 20 set of parameter estimates*/

proc reg data= &data;

class b c ;

model e= a b c d/ solution ss3;

run;

%mend test;

Re: Macro Code for splitting data-set and running Proc Reg on each data-set

RW9 — Wed, 17 Dec 2014 11:37:17 GMT

Well, way I would do it is something like (and note that the second call execute with the proc reg, I have just copied from yours, I have not tested that part) this which uses the number of observations from the vtable metadata and loops, generating the code for each block:

data have;
do a=1 to 100000;
b=a; c=a; d=a; e=a; output;
end;
run;
data total;
/* set this as your final table - I don't know the strucutre off the top of my head */
run;

data _null_;
set sashelp.vtable (where=(libname="WORK" and memname="HAVE"));
do i=0 to nobs by 5000;
    call execute('data temp; set have; if '||strip(put(i,best.))||' <= _n_ < '||strip(put(i+5000,best.))||' then output; run;');
    call execute('ods output ParameterEstimates=parm;
                  proc reg data= temp;
                    class b c ;
                    model e= a b c d/ solution ss3;
                  run;
                  data total;
                    set total parm;
                  run;');
end;
run;

Re: Macro Code for splitting data-set and running Proc Reg on each data-set

data_null__ — Wed, 17 Dec 2014 13:55:34 GMT

You don't need to split anything or gen any code. You can use a view to create a grouping variable and run PROC GLM with the BY statement. And since nothing was split nothing needs to be re-assembled. You will need GLM or perhaps MIXED; REG does not have CLASS statement.

data heartv / view=heartv;
   length group 8; 
   set sashelp.heart;
   if mod(_n_,100) eq 1 then group +1; 
   run; 

*ods trace on;
ods select none; 
proc glm data=heartv;
   by group;
   class Status;
   model weight = AgeAtStart Height Diastolic Systolic MRW Status / solution ss3; 
   ods output ParameterEstimates=ParameterEstimates;
   run; 
*ods trace off;
ods select all; 
proc print data=ParameterEstimates;
   run;