run a complex query in do loop

[harsh0404]

data table;
do initiative = 1701 to 1704;


PROC RANK DATA = table
     DESCENDING
     GROUPS=10
     TIES=MEAN
     OUT=table2;
     VAR var1;
RANKS Decile ;

proc tabulate data=table;
   class decile var2 ;
   var var3;
   table decile=' ', 
        var2 *var3=''*mean=''
         /misstext=' ' box=decile;
run; 


output ; 
end ; 
run ;

I am running a do loop. I have a variable called initiative which has many values. I want to run a do loop, where it first fetches 1701 value and ranks the data of the file with initiative = 1701 (and also creates decile) and then it creates a table. 

This part if right of ranking the values and creating table I want. However something is wrong with do loop part. 

Can someone please advise what's wrong here? 

I want to run this looping from 1701, 1702. 1703, 1704 and create tables for all 4 initiatives. 

Some more clarification - This query in the middle is huge, I have just written a part of it. So aplogies. 

inside the do loop, I use variable initiative a lot times. 

i need to use do loop here becuase i have values of initiative from 1901 to 2005. 

I have just tried to simplify this code. 

[Reeza]

1. You never use initiative in your code, where would you expect that to be implemented?

You should be using BY groups instead.

proc sort data=table;
by initiative;
run;

PROC RANK DATA = table
     DESCENDING
     GROUPS=10
     TIES=MEAN
     OUT=table2;

*assumes variables are numeric;
where initiative in (1701:1704);

BY initiative;

     VAR var1;
RANKS Decile ;

proc tabulate data=table;
by initiative;
   class decile var2 ;
   var var3;
   table decile=' ', 
        var2 *var3=''*mean=''
         /misstext=' ' box=decile;
run; 

---

@harsh0404 wrote:

data table;
do initiative = 1701 to 1704;


PROC RANK DATA = table
     DESCENDING
     GROUPS=10
     TIES=MEAN
     OUT=table2;
     VAR var1;
RANKS Decile ;

proc tabulate data=table;
   class decile var2 ;
   var var3;
   table decile=' ', 
        var2 *var3=''*mean=''
         /misstext=' ' box=decile;
run; 


output ; 
end ; 
run ;

I am running a do loop. I have a variable called initiative which has many values. I want to run a do loop, where it first fetches 1701 value and ranks the data of the file with initiative = 1701 (and also creates decile) and then it creates a table. 

This part if right of ranking the values and creating table I want. However something is wrong with do loop part. 

Can someone please advise what's wrong here? 

I want to run this looping from 1701, 1702. 1703, 1704 and create tables for all 4 initiatives. 

---

[harsh0404]

Some more clarification - This query in the middle is huge, I have just written a part of it. So apologies. 

inside the do loop, I use variable initiative a lot times. 

i need to use do loop here becuase i have values of initiative from 1901 to 2005. 

I have just tried to simplify this code. 

[Reeza]

Please expand your sample code/query to be more reflective of your situation.

Otherwise, the tutorial above, about converting working code to a macro is what you want.

[ballardw]

---

@harsh0404 wrote:

Some more clarification - This query in the middle is huge, I have just written a part of it. So apologies. 

inside the do loop, I use variable initiative a lot times. 

i need to use do loop here becuase i have values of initiative from 1901 to 2005. 

I have just tried to simplify this code. 

---

One suspects that you may be dealing with a year/month construct as the values of your initiative variable. If that is the case then you have a problem with any iterated loop as your values should skip everything between 1912 and 2001. Is this indeed the case?

[Tom]

Your code doesn't make any sense as written. The data step BEFORE the first PROC is doing nothing. And the statements after the last PROC are not part of any data step.

Sounds like you just want to use BY group processing.

You did not show what your data actually looks like (or even what the name of the dataset is).  Let's assume that the dataset is named HAVE and there is a variable in it named INITIATIVE. Also let's assume that it is already sorted by INITIATIVE.

PROC RANK DATA = HAVE
     DESCENDING
     GROUPS=10
     TIES=MEAN
     OUT=table2
;
  VAR var1;
  BY initiative;
  RANKS Decile ;
run;

If you want to limit it to just those four values of INITIATIVE then add a WHERE statement to the proc step.

[Reeza · Accepted solution]

In general, you cannot use a data step to loop procedures like that. That would require the use of macros or by groups. In this case, I suspect BY group processing is better but if you want to learn about macro's here's some good references:

Macro Language References

UCLA introductory tutorial on macro variables and macros

https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/

Tutorial on converting a working program to a macro

This method is pretty robust and helps prevent errors and makes it much easier to debug your code. Obviously biased, because I wrote it 🙂 https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

---

@harsh0404 wrote:

data table;
do initiative = 1701 to 1704;


PROC RANK DATA = table
     DESCENDING
     GROUPS=10
     TIES=MEAN
     OUT=table2;
     VAR var1;
RANKS Decile ;

proc tabulate data=table;
   class decile var2 ;
   var var3;
   table decile=' ', 
        var2 *var3=''*mean=''
         /misstext=' ' box=decile;
run; 


output ; 
end ; 
run ;

I am running a do loop. I have a variable called initiative which has many values. I want to run a do loop, where it first fetches 1701 value and ranks the data of the file with initiative = 1701 (and also creates decile) and then it creates a table. 

This part if right of ranking the values and creating table I want. However something is wrong with do loop part. 

Can someone please advise what's wrong here? 

I want to run this looping from 1701, 1702. 1703, 1704 and create tables for all 4 initiatives. 

---