DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

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

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.


Accepted Solutions
Solution
Monday
Super User
Super User
Posts: 8,093

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

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;

 

View solution in original post


All Replies
Respected Advisor
Posts: 3,001

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

[ Edited ]

@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.

--
Paige Miller
New Contributor
Posts: 2

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

Posted in reply to PaigeMiller

Paige,

 

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

Respected Advisor
Posts: 3,001

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

I have no idea what this means.

--
Paige Miller
Super User
Posts: 13,523

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


@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.

 

Solution
Monday
Super User
Super User
Posts: 8,093

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

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;

 

Super User
Posts: 6,765

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

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?

Super User
Posts: 23,700

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

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.


 

Super User
Posts: 23,700

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

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.


 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 86 views
  • 0 likes
  • 6 in conversation