Use Macro Variable Who's Value is Part of Existing Variable Name

[neml_fm]

I have a table of which consists of a unique identifier, a records count, and two sets of 8 fields that are numbered 1 through 8.

I want to separate the one large table into 8 separate tables with the unique identifier, the record count, and the two fields with the same number.  I tried using a macro but it is not working and I don't know enough about macros to know how to do it properly. 

%macro pull_rank(rnk_nbr=);

data mjr_def_rank_&rnk_nbr.;
      keep nbr_ssr rvw_cnt mjr_rnk_&rnk_nbr rnk_opn_mjr_&rnk_nbr;
   set mjr_def_cnts_x_ssr;
run;

%mend pull_rank;

%pull_rank(rnk_nbr=1);
%pull_rank(rnk_nbr=2)
%pull_rank(rnk_nbr=3)
%pull_rank(rnk_nbr=4)
%pull_rank(rnk_nbr=5)
%pull_rank(rnk_nbr=6)
%pull_rank(rnk_nbr=7)
%pull_rank(rnk_nbr=8)

I am aware of do loops with n repetitions but have no experience with them.  Any help you could give me would be greatly appreciated.  I have had no in depth training so I apologize for the simplicity of my problem.

[PaigeMiller]

---

@neml_fm wrote:

I have a table of which consists of a unique identifier, a records count, and two sets of 8 fields that are numbered 1 through 8.

I want to separate the one large table into 8 separate tables with the unique identifier, the record count, and the two fields with the same number.

---

This is almost always a bad idea. Programming so much easier if everything is in one data set and you use BY statements to analyze the data by your variable rank_nbr. So, I believe this is a solution to your macro problem.

[neml_fm]

Paige,

So how do I get varA_1 and varB_1 by comparing it to rank_nbr where it equals 1? 

[PaigeMiller]

I have no idea what this means.

[ballardw]

---

@neml_fm wrote:

Paige,

So how do I get varA_1 and varB_1 by comparing it to rank_nbr where it equals 1? 

---

Since your "example" doesn't reference varA_1 or varB_1 this is adding another layer of confusion to any issue.

What does "get" mean in this context?

If I have two sets of variables with similar names and I want to do something using them as pairs the typical approach is to use arrays which are short hand ways to reference groups of variables.

data have;
   input id a1 b1 a2 b2;
datalines;
1 23 47 16 9
2 1.5 3.8 25 66
;
run;

data want;
   set have;
   array a a1-a2;
   array b b1-b2;
   array sum{2};
   do i = 1 to dim(a);
      sum[i] = a[i] + b[i];
   end;
run;

If my dataset Have above had 15 a and 15 b variables that I want to sum I would change the a2, b2 and sum{2} to a15, b15 and sum{15};

The first two arrays will use defined variables in the list if they exist. The array sum creates variables sum1 sum2 ,,, sumx whatever the integer inside the {} might be.

The do loop counts how many elements are in a and uses the corresponding b to create the sum of the pair.

So you could use your rank_nbr as the index value of the array element (the number inside the [ ]) . I think.

Another thing the arrays might be used for is to restructure the data:

data trans;
   set have;
   array a a1-a2;
   array b b1-b2;
   array sum{2};
   do i = 1 to dim(a);
      atrans= a[i];
      btrans= b[i];
      output;
   end;
   keep id i atrans btrans;
run;

which reshapes the data so that ID has one a and one b value per row and an indicator (I) which of the pairs contributed to the current row.

[Tom · Accepted solution]

What are you going to do with these copies of the data that you couldn't do with the original data?

For example instead of using one of these copies using code like:

proc print data=mjr_def_rank_1 ;
run;

You could instead just use the original dataset by using code like this:

proc print data=mjr_def_cnts_x_ssr  (keep= nbr_ssr rvw_cnt mjr_rnk_1 rnk_opn_mjr_1);
run;

Or this: 

proc print data=mjr_def_cnts_x_ssr ;
  var nbr_ssr rvw_cnt mjr_rnk_1 rnk_opn_mjr_1 ;
run;

 You can use a data step to generate code if you really wanted to do it.

data _null_;
  do i=1 to 8 ;
     call execute(cats('%nrstr(%pull_rank)(rnk_nbr=',i,')'));
  end;
run;

[Astounding]

This covers far too broad a range to be helpful:  "but it is not working"

What actually happens when you try the program you posted?

[Reeza]

Are you trying to change your data structure? It looks like you're either splitting your data for export or restructuring. 

If you're splitting it for process, that's almost always unneccessary. 

If you think you really really need it, search "sas subset data" and you'll find many examples either on here or SAS blogs. 

---

@neml_fm wrote:

I have a table of which consists of a unique identifier, a records count, and two sets of 8 fields that are numbered 1 through 8.

I want to separate the one large table into 8 separate tables with the unique identifier, the record count, and the two fields with the same number.  I tried using a macro but it is not working and I don't know enough about macros to know how to do it properly. 

%macro pull_rank(rnk_nbr=);

data mjr_def_rank_&rnk_nbr.;
      keep nbr_ssr rvw_cnt mjr_rnk_&rnk_nbr rnk_opn_mjr_&rnk_nbr;
   set mjr_def_cnts_x_ssr;
run;

%mend pull_rank;

%pull_rank(rnk_nbr=1);
%pull_rank(rnk_nbr=2)
%pull_rank(rnk_nbr=3)
%pull_rank(rnk_nbr=4)
%pull_rank(rnk_nbr=5)
%pull_rank(rnk_nbr=6)
%pull_rank(rnk_nbr=7)
%pull_rank(rnk_nbr=8)

I am aware of do loops with n repetitions but have no experience with them.  Any help you could give me would be greatly appreciated.  I have had no in depth training so I apologize for the simplicity of my problem.

---

[Reeza]

And the actual answer to your question, try the following:

options mprint mlogic;
%macro pull_rank(rnk_nbr=);

data mjr_def_rank_&rnk_nbr.;
      set mjr_def_cnts_x_ssr;

      keep nbr_ssr rvw_cnt mjr_rnk_&rnk_nbr. rnk_opn_mjr_&rnk_nbr.;
run;

%mend pull_rank;

If it doesn't work, post your full log from a single call. 

---

@neml_fm wrote:

I have a table of which consists of a unique identifier, a records count, and two sets of 8 fields that are numbered 1 through 8.

I want to separate the one large table into 8 separate tables with the unique identifier, the record count, and the two fields with the same number.  I tried using a macro but it is not working and I don't know enough about macros to know how to do it properly. 

%macro pull_rank(rnk_nbr=);

data mjr_def_rank_&rnk_nbr.;
      keep nbr_ssr rvw_cnt mjr_rnk_&rnk_nbr rnk_opn_mjr_&rnk_nbr;
   set mjr_def_cnts_x_ssr;
run;

%mend pull_rank;

%pull_rank(rnk_nbr=1);
%pull_rank(rnk_nbr=2)
%pull_rank(rnk_nbr=3)
%pull_rank(rnk_nbr=4)
%pull_rank(rnk_nbr=5)
%pull_rank(rnk_nbr=6)
%pull_rank(rnk_nbr=7)
%pull_rank(rnk_nbr=8)

I am aware of do loops with n repetitions but have no experience with them.  Any help you could give me would be greatly appreciated.  I have had no in depth training so I apologize for the simplicity of my problem.

---