topic Re: SAS Macro & Nested Do Loop to subset data in SAS Programming

SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:02:00 GMT

Hello Everyone:

I have a big dataset, and I want to create subsets from this dataset and combine all the subsets into a separate dataset. Here are more details:

corvar1 through corvar12: 12 different dummy variables. Each variable takes the value of 0 or 1.

allmdummy: a dummy variable that takes the value of 0 or 1.

I want to create 108 different subsets. For example, for corvar1, I would like to create the following nine subsets:

corvar1 le 1 + allmdummy le 1 (include all values of corvar1 and all values of allmdummy)

corvar1 le 1 + allmdummy eq 1

corvar1 le 1 + allmdummy lt 1

corvar1 eq 1 + allmdummy le 1

corvar1 eq 1 + allmdummy eq 1

corvar1 eq 1 + allmdummy lt 1

corvar1 lt 1 + allmdummy le 1

corvar1 lt 1 + allmdummy eq 1

corvar1 lt 1 + allmdummy lt 1 (include only the values where corvar1=0 and allmdummy=0).

Below is a rough sketch of what I would like to accomplish. I would love to have your help with improving this code.

Thank you so much!

%macro groupa;
%do cvar=1 %to 12; /*names for the variables*/
%do m=1 %to 3; /*for math operations*/
%do n=1 %to 3; /*for math operations*/

data set&cvar&m&n; set regsample;
if &m=1 then opa="le";
if &m=2 then opa="eq";
if &m=3 then opa="lt";

if &n=1 then opb="le";
if &n=2 then opb="eq";
if &n=3 then opb="lt";

if corvar&cvar opa 1 and allmdummy opb 1;
id1=&cvar;
id2=&m;
id3=&n;
sampleid=&cvar&m&n; /*create a sample ID*/
proc append base=wanted data=set&cvar&m&n; /*I want to combine all the datasets into a single one. Not sure if this syntax is right*/
%end;

%end;

%end;
%mend groupa;
%groupa

Re: SAS Macro & Nested Do Loop to subset data

PaigeMiller — Wed, 20 Sep 2023 14:09:28 GMT

@finans_sas wrote:

I have a big dataset, and I want to create subsets from this dataset and combine all the subsets into a separate dataset.

Why do this? Why not leave the data set as one big data set, then no splitting and re-combining is needed?

In general, splitting large data sets into smaller data sets is a sub-optimal approach in most situations.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:24:43 GMT

Thank you so much, @PaigeMiller for your prompt reply. You made an excellent point. It is a nice segway into my end goal. I actually want to run the following for each sampleid (assuming that wanted is the output of the prior macro). If I could find a way to incorporate the prior macro into the next one, that will indeed save me a lot of time. I will really appreciate any insight on this one.

proc reg data=wanted noprint outest=wanted2 noprint adjrsq;
by sample id obsid; /*obsid is another ID already stored in the dataset*/
model depvar1 = ind1 ind2 ind3 ind4 / hcc;
run;

proc means data=wanted2;

by sampleid;
var intercept ind1 ind2 ind3 ind4 _rsq_ _adjrsq_;
output out=wanted3 mean = std= t= probt= / autoname;
run;

Re: SAS Macro & Nested Do Loop to subset data

PaigeMiller — Wed, 20 Sep 2023 14:29:10 GMT

It's still not clear to me why you would not simply take the big data set, assign appropriate values to variable SampleID (without splitting up the big data set), and then use this big data set which now has values for SampleID in PROC MEANS and PROC REG.

Re: SAS Macro & Nested Do Loop to subset data

Quentin — Wed, 20 Sep 2023 14:30:24 GMT

As @PaigeMiller often asks (wisely), what comes next after you have replicated your data in this way? It looks like you are creating subsets (non-exclusive) and then stacking them back together. There are situations where this is useful (e.g. for simulations), but this may not be one of those situations.

If you want to analyze data for different subsets, you can leave the data as is, and then use a WHERE statement to identify subsets of interest. e.g.:

proc whatever; 
  where corvar1 le 1 and  allmdummy le 1 ;
run;

If you really want 108 different subsets, you could generate it with code like:

data big;
  set have;
  if corvar1 le 1 and allmdummy le 1 then do;
    subset=1;
    output; 
  end;
  
  if corvar1 le 1 and allmdummy eq 1 then do;
    subset=2;
    output; 
  end;

  *...;
run;

And you might use a macro or some other code generation technique to create the list of IF statements.

Your current IF statement:

if corvar&cvar opa 1 and allmdummy opb 1;

Can't work because the data step compiler won't see the operators when the step tries to compile.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:36:37 GMT

Thank you so much once again for your quickly reply, @PaigeMiller . Here is my challenge: the subsets will be overlapping. For example, sampleid 111 will be a combination of 112 and 113. I could not figure out how to create sample IDs without overwriting the existing ones.

Re: SAS Macro & Nested Do Loop to subset data

PaigeMiller — Wed, 20 Sep 2023 14:45:28 GMT

@finans_sas wrote:

Thank you so much once again for your quickly reply, @PaigeMiller . Here is my challenge: the subsets will be overlapping. For example, sampleid 111 will be a combination of 112 and 113. I could not figure out how to create sample IDs without overwriting the existing ones.

One could, without writing macros to split up large data sets, write a data step which created (potentially) multiple observations from one original observation. So if an original observation winds up in 112 and 111, the data step creates two observations, with appropriate sample IDs. Then you sort this data by SampleID and then run your PROC MEANS and PROC REG. And then no splitting of the data set and recombining is needed, and no macros are needed.

For PROC MEANS (but not PROC REG as far as I know) you could also use MULTILABEL formats for this (example).

I do not have time to help you write such code at this time, but I'm sure there are plenty of people here who can help you with this.

This does bring up the interesting problem, if you have 108 regressions, how do you use these in some logical way? What is the point of generating 108 regressions in the first place?

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:47:33 GMT

Thank you so much for your suggestions, @Quentin . If I could find a way to do the proc reg without splitting the dataset, that will actually be awesome since the splitting and re-combining will take a massive amount of hard disk space. The challenge I have at this point is that some observations will fall under more than one sample ID due to overlapping subsets.

Re: SAS Macro & Nested Do Loop to subset data

Tom — Wed, 20 Sep 2023 15:29:07 GMT

Put 108 WHERE clauses into a character variable in a dataset.

Either just type them out

data samples;
  sampleid+1;
  input where $200.;
cards;
corvar1 le 1 and allmdummy le 1 
corvar1 le 1 and allmdummy eq 1
corvar1 le 1 and allmdummy lt 1
corvar1 eq 1 and allmdummy le 1
corvar1 eq 1 and allmdummy eq 1
corvar1 eq 1 and allmdummy lt 1
corvar1 lt 1 and allmdummy le 1
corvar1 lt 1 and allmdummy eq 1
corvar1 lt 1 and allmdummy lt 1 
;

or generate them if there is pattern.

Then create a macro for the analysis that takes SAMPLEID and WHERE as inputs. The macro could use &WHERE to generate a WHERE statement.

proc reg data=have;
  where &where ;
....

So say the macro is named ANAL you could then call it 108 times with code like this:

data _null_;
  set samples;
  call execute(cats('%nrstr(%anal)(sampleid=',sampleid,',where=',where,')'));
run;

The macro could include PROC APPEND step(s) to aggregate the results into a single dataset.

Re: SAS Macro & Nested Do Loop to subset data

Quentin — Wed, 20 Sep 2023 17:35:01 GMT

@finans_sas wrote:

Thank you so much for your suggestions, @Quentin . If I could find a way to do the proc reg without splitting the dataset, that will actually be awesome since the splitting and re-combining will take a massive amount of hard disk space. The challenge I have at this point is that some observations will fall under more than one sample ID due to overlapping subsets.

The DATA step I posted will work fine with overlapping subsets. Each record is output once for each subset it is in. And you could use a macro or whatever to generate the 108 if-then blocks. Then you could run one PROC REG on the dataset, BY SAMPLEID.

But yes, it will take lots of disk space.

Take a look at Tom's suggestion of putting your control data (WHERE expressions) in a dataset, and then using that to drive macro calls to do 108 PROC REG steps.

Re: SAS Macro & Nested Do Loop to subset data

PaigeMiller — Wed, 20 Sep 2023 18:00:26 GMT

I'd still like an answer to this: "This does bring up the interesting problem, if you have 108 regressions, how do you use these in some logical way? What is the point of generating 108 regressions in the first place?"

Re: SAS Macro & Nested Do Loop to subset data

Reeza — Wed, 20 Sep 2023 19:10:18 GMT

Each variable takes the value of 0 or 1.

allmdummy: a dummy variable that takes the value of 0 or 1.

Are these two true?

If so, you're looking for combinations of (0, 1, 0/1) versus 0,1, 0/1) to make all your combinations?

Re: SAS Macro & Nested Do Loop to subset data

PaigeMiller — Wed, 20 Sep 2023 19:44:59 GMT

Partial code, without macros, no pulling data set apart and then later combining data sets

data want;
    set regsample;
    length sampleid $ 4;
    array corvar(12) corvar1-corvar12;
    do i=1 to 12;
         if corvar(i) le 1 and alldummy le 1 then do;
             sampleid=cats(i,1,1);
             output;
         end;
         if corvar(i) eq 1 and alldummy le 1 then do;
             sampleid=cats(i,2,1);
             output;
         end;
         /* You write the rest, there are only 9 possibilities here, as m=1,2,3 and n=1,2,3 */
    end;
run; 
proc sort data=want;
    by sampleid obsid;
run;
proc reg data=want noprint outest=wanted2 noprint adjrsq;
    by sampleid obsid;    /*obsid is another ID already stored in the dataset*/
    model depvar1 = ind1 ind2 ind3 ind4 / hcc;
run;
proc means data=want;
    by sampleid;
    var intercept ind1 ind2 ind3 ind4 _rsq_ _adjrsq_;
    output out=wanted3 mean = std= t= probt= / autoname;
run;

This code passes through data set REGSAMPLE once. Your original code (pull data set apart, combine them all together later) passes through REGSAMPLE 108 times.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 20:23:11 GMT

Thank you @Reeza for this question. Your statement is correct. The combinations will 0, 1, and 0&1 together versus 0, 1, and 0&1 together. I have 12 variables (corvar1 through corvar12), and each corvar takes the values of 0 and 1. And the other dummy, allmdummy, takes the values of 0 and 1 too. I want to be able to run the same regression for every single combination, which amounts to 108. All the possible combinations for the first variable will look like the following:

corvar1=0 and allmdummy=0,

corvar1=0 and allmdummy=1

corvar1=0 and allmdummy= 0 or 1

corvar1=1 and allmdummy=0,

corvar1=1 and allmdummy=1

corvar1=1 and allmdummy= 0 or 1

corvar1=0,1, allmdummy=0,

corvar1=0,1 allmdummy=1

corvar1=0,1 allmdummy= 0 or 1

Ideally, I will be able to create a unique sampleid for each combination and then run proc reg and proc means command for each combination (sampleid). I hope this helps. Please let me know if you have any follow-up questions.

Thank you so much!

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 20:26:16 GMT

Quick clarification: by "0 or 1" and "0,1" I meant that we include all the values where the variable is either 0 or 1, which by definition is all the observations for a given dummy variable.

Re: SAS Macro & Nested Do Loop to subset data

PaigeMiller — Wed, 20 Sep 2023 21:16:43 GMT

@finans_sas wrote:

Thank you @Reeza for this question. Your statement is correct. The combinations will 0, 1, and 0&1 together versus 0, 1, and 0&1 together. I have 12 variables (corvar1 through corvar12), and each corvar takes the values of 0 and 1. And the other dummy, allmdummy, takes the values of 0 and 1 too. I want to be able to run the same regression for every single combination, which amounts to 108. All the possible combinations for the first variable will look like the following:

corvar1=0 and allmdummy=0,

corvar1=0 and allmdummy=1

corvar1=0 and allmdummy= 0 or 1

corvar1=1 and allmdummy=0,

corvar1=1 and allmdummy=1

corvar1=1 and allmdummy= 0 or 1

corvar1=0,1, allmdummy=0,

corvar1=0,1 allmdummy=1

corvar1=0,1 allmdummy= 0 or 1

Ideally, I will be able to create a unique sampleid for each combination and then run proc reg and proc means command for each combination (sampleid). I hope this helps. Please let me know if you have any follow-up questions.

Thank you so much!

This is a trivial change to the solution I posted earlier.

Re: SAS Macro & Nested Do Loop to subset data

Reeza — Wed, 20 Sep 2023 21:25:21 GMT

You're approaching inefficiently in my view.

You should transpose your data such that all of the variables you want to filter by are one variable with another variable holding the variable name.

Then I think you can run two or three regressions using a BY statement instead of macros or loops anywhere.

Your data should be something like this, with the transpose replicating your data for you. The overlaps are handled with the different multiple regressions.

VariableName VariableValue AllMDummy ........
ColVar1   0  0
ColVar2 1 0
...

Then run:

proc reg data=transposed;
by variableName VariableValue;
.....;
run;

proc reg data=transposed;
by variableName VariableValue ALLMDummy;
.....;
run;

proc reg data=originalData;
*NO BY statement, your 0/1 * 0/1 results in all included but use originalData;
run;

@finans_sas wrote:

Thank you @Reeza for this question. Your statement is correct. The combinations will 0, 1, and 0&1 together versus 0, 1, and 0&1 together. I have 12 variables (corvar1 through corvar12), and each corvar takes the values of 0 and 1. And the other dummy, allmdummy, takes the values of 0 and 1 too. I want to be able to run the same regression for every single combination, which amounts to 108. All the possible combinations for the first variable will look like the following:

corvar1=0 and allmdummy=0,

corvar1=0 and allmdummy=1

corvar1=0 and allmdummy= 0 or 1

corvar1=1 and allmdummy=0,

corvar1=1 and allmdummy=1

corvar1=1 and allmdummy= 0 or 1

corvar1=0,1, allmdummy=0,

corvar1=0,1 allmdummy=1

corvar1=0,1 allmdummy= 0 or 1

Ideally, I will be able to create a unique sampleid for each combination and then run proc reg and proc means command for each combination (sampleid). I hope this helps. Please let me know if you have any follow-up questions.

Thank you so much!

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Thu, 21 Sep 2023 00:15:14 GMT

Thank you so much for sharing this other perspective, @Reeza . I need to think about how it will work with the data. The starting dataset has around 50 million rows of data (another one that I need to apply the same code to has about 250 million).

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Thu, 21 Sep 2023 00:20:53 GMT

Great question, @PaigeMiller ! This is related to the need to show that the results are robust to different ways of measuring the variable of interest and to the different subsamples. Most of them will be a footnote anyways but we need to perform them.

Re: SAS Macro & Nested Do Loop to subset data

Quentin — Thu, 21 Sep 2023 01:35:55 GMT

@finans_sas wrote:

Thank you so much for sharing this other perspective, @Reeza . I need to think about how it will work with the data. The starting dataset has around 50 million rows of data (another one that I need to apply the same code to has about 250 million).

That's an exciting twist to the story... If you take one of the approaches where you expand the data to have one record per SampleID, with 108 different sampleIDs, do you have a sense of how much that will increase the number of rows? Each record can't be in all 108 samples, so it wouldn't be 50M*108, but could it be 50M*20?

That's getting big. I don't work with big data much, but I'd be inclined to try Tom's approach of just running 108 PROC REG steps (one for each subset), rather than expanding the data into billions of rows. And hopefully you've got enough memory...