A SAS foreach loop?

[raivester]

I am wondering if there is a way to initialize some sort of foreach loop that begins outside a series of data and proc steps. After running the first data step below, I want to run all the subsequent data and proc steps for each of the four states listed in the first data set. Specifically, I want to do something like: for the first state do steps 2-6, then repeat for state 2, state 3 and state 4.

data part_a;
 	set annual.ncrp_ab_2018;
  	keep year state state_code;
  	year = 2018;
  	if stcode = "01" then state = "AL" ;
	if stcode = "02" then state = "AK" ;
	if stcode = "04" then state = "AZ" ;
	if stcode = "05" then state = "AR" ;
	
  	length state_code $43;

  	array var_array var1 var2 var3;

  	do a=1 to 3;
	  	state_code = var_array[a];
		output;
  	end;

 run;

 data part_a;
 	set part_a;
	if state_code NE " " and state = "[STATE1]";
 run;
  
 proc sort data=part_a nodupkey;
 	by _all_;
 run;

data state_AL;
	set offense.[STATE1];
	if state_code NE " ";
run;

proc sort data=state_[STATE1];
	by state_code;
run;

data offense_state_[STATE1];
	merge part_a(in=inA state_AL(in=inB);
	by state_code;
run;

[PaigeMiller]

Chopping up data sets like this is usually inefficient to run, and inefficient to program. Why would you want code that performs some task on Alabama data, and then another block of code that performs the same task on the Alaska data?

Depending on what you are planning to do, there are many approaches that get the job done without chopping up the data into many datasets that then have to be handled individually.

Also, in most PROCs, the SAS equivalent of "foreach" is to use a BY statement.

[raivester]

The state associated with an observation in the original data set determines which of 50 sheets it needs to merge with in separate crosswalk document.

[PaigeMiller · Accepted solution]

---

@raivester wrote:
The state associated with an observation in the original data set determines which of 50 sheets it needs to merge with in separate crosswalk document.

---

Read each of the 50 sheets into SAS, combine into a SAS data set and then do one merge, and from that point on, no need for separate data sets for each state.

[raivester]

Oh right! That would work. Is there a way for me to create a variable equal to the state abbreviation as I read in and append each state's sheet? It's like I want to do the following, but there doesn't appear to be an option for generate a variable in the same way there is to rename one as you read it in. I'd ultimately need this variable to merge.

data xwalk_appended;
     set offenses.AK(var=(state="AK")) offenses.AL(var=(state="AL"));
run;

[PaigeMiller]

If the state name of AK is not in the Alaska table, and similarly for all the other tables, you could modify the tables to have the state name in the tables before concatenating them.

Otherwise, it would take a bit of typing, but still preferable to DO loops or similar.

data newtable;
    set offenses.al(in=in1) offenses.ak(in=in2) ... ;
    if in1 then state='AL';
    else if in2 then state='AK';
    ...
;

[ballardw]

The first thing I would say is to check that first data step.

I suspect that there is a chance that state_code is missing.

If not you might want to investigate Proc Transpose.

All that

if stcode = "01" then state = "AL" ;

Could likely be replaced with a custom format for the value of stcode to show the state abbreviation.

You  might consider showing some example data and the desired result at the end (though maybe not as 50 different data sets per @PaigeMiller's comments).

I have a sneaking suspicion the major combination of the data can be done with a single call to Proc Sql.

Then process the desired analysis/ summary/ graphing with by group processing.

You might also consider a data set with the values you need to control things and use PUT statements with a FILE to write the code lines into a program file you can execute after checking that it has the code you want.

Or you can look up the data set statement CALL EXECUTE to create code based on the values of variables in a data set.

[PaigeMiller]

Great comments from @ballardw. You see, @raivester, there are  structures within SAS that let you do what you want efficiently both in terms of programming and in terms of execution. But if you frame the problem as you must have a loop, or you must use something like "foreach", you wind up heading down a very inefficient path.