DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 2

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

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;

Super User
Super User
Posts: 7,997

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

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;

Respected Advisor
Posts: 3,799

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

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;
12-17-2014 7-51-47 AM.png
Ask a Question
Discussion stats
  • 2 replies
  • 343 views
  • 1 like
  • 3 in conversation