topic subsetting data and copying values based on conditions using loop in SAS Programming

subsetting data and copying values based on conditions using loop

wfung — Tue, 18 Jun 2024 16:11:35 GMT

I'm working on a SAS program that's long and was wondering if I can convert the following codes into loops so that it's easier for others to read and review/check. (I changed to generic variables for simplicity.)

I'm also learning loops so trying to use it as much as possible to get some practice.

In this section, I subset the data into different datasets based on the value of a variable.

data
	Locations1 
	Locations2
	Locations3
	Locations4  
	Locations5
	Locations6
	Locations7
	Locations8
	Locations9
	Locations10;
set data_all; 
if number_of_addresses = 1  then output Locations1;  
if number_of_addresses = 2  then output Locations2;  
if number_of_addresses = 3  then output Locations3; 
if number_of_addresses = 4  then output Locations4; 
if number_of_addresses = 5  then output Locations5; 
if number_of_addresses = 6  then output Locations6; 
if number_of_addresses = 7  then output Locations7; 
if number_of_addresses = 8  then output Locations8; 
if number_of_addresses = 9  then output Locations9; 
if number_of_addresses = 10 then output Locations10; 
run;

In this section, I'm calculating percentage based on the value of a variable.

data percent;
set data_all; 
if A <= 2 then percent_2 = num_employees / totals2; 
if A <= 3 then percent_3 = num_employees / totals3; 
if A <= 4 then percent_4 = num_employees / totals4; 
if A <= 5 then percent_5 = num_employees / totals5; 
if A <= 6 then percent_6 = num_employees / totals6; 
if A <= 7 then percent_7 = num_employees / totals7; 
if A <= 8 then percent_8 = num_employees / totals8; 
if A <= 9 then percent_9 = num_employees / totals9; 
if A <= 10 then percent_10 = num_employees / totals10; 
run;

Finally, here I'm just copying values from multiple variables into one variable based on the value of a variable.

data visit;
set data_all; 
if G = 2 then V = V_all2; 
if G = 3 then V = V_all3; 
if G = 4 then V = V_all4; 
if G = 5 then V = V_all5; 
if G = 6 then V = V_all6; 
if G = 7 then V = V_all7; 
if G = 8 then V = V_all8; 
if G = 9 then V = V_all9; 
if G = 10 then V = V_all10; 
run;

Thanks in advance!

Re: subsetting data and copying values based on conditions using loop

PaigeMiller — Tue, 18 Jun 2024 16:25:23 GMT

Some comments:

Splitting data into separate data sets is usually something to avoid. It is a poor practice that is inefficient and makes your coding a lot more tedious. Even if you could do this with loops, one of the things you need to understand is when to split the data and when not to split the data, and I wouldn't bother learning loops here.
Your second block of code seems like a simple array.
Your third block of code seems like a simple array.
However, these examples seem unrealistic to me, that I have never done anything like this in real life. It would be helpful to for you to find more realistic data sets and examples.

Re: subsetting data and copying values based on conditions using loop

PaigeMiller — Tue, 18 Jun 2024 16:55:07 GMT

Here's a more realistic example where loops might be necessary. Data set here. Suppose you want to find the number of times in the variables F1-F30 where F(i) > F(i-1) and the number of times where F(i) is 10% greater (or more) than F(i-1)

data want;
    set ftest;
    array f f1-f30;
    greater_than=0;
    ten_percent_greater=0;
    do i=2 to dim(f);
         if f(i)>f(i-1) then greater_than = greater_than+1;
         if f(i)>=1.1*f(i-1) then ten_percent_greater=ten_percent_greater+1;
    end;
run;

Re: subsetting data and copying values based on conditions using loop

Reeza — Tue, 18 Jun 2024 16:53:07 GMT

For #1 pretty much follow this example

https://gist.github.com/statgeek/4bfb7574713bedf4e011

Sort data set by splitting variable
Generate data step code to split file
CALL Execute splits file

	proc sort data=data_all;
	by num_of_addresses;
	run;

	data _null_;
	set data_all;
	by num_of_addresses;

	if first.num_of_addresses then do;


		*create string;
		str1='data LOCATIONS'||put(num_of_addresses ,z3.)||"; set data_all; where num_of_addresses ="||put(num_of_addresses , 2.)||";run;";
		call execute(str1);

	end;
	run;

For #2/3 use arrays.

Here's a tutorial on using Arrays in SAS
https://stats.idre.ucla.edu/sas/seminars/sas-arrays/

data percent;
set data_all;

array _per(2:10) percent_2-percent_10;
array _tot(2:10) totals2-totals10;
array _v(2:10) v_all2-v_all10;

do i=2 to 10;
  _per(i) = num_employees/_tot(i);

if G=i then V = _v(i);
   
end;

run;

@wfung wrote:

I'm also learning loops so trying to use it as much as possible to get some practice.

In this section, I subset the data into different datasets based on the value of a variable.

data
	Locations1 
	Locations2
	Locations3
	Locations4  
	Locations5
	Locations6
	Locations7
	Locations8
	Locations9
	Locations10;
set data_all; 
if number_of_addresses = 1  then output Locations1;  
if number_of_addresses = 2  then output Locations2;  
if number_of_addresses = 3  then output Locations3; 
if number_of_addresses = 4  then output Locations4; 
if number_of_addresses = 5  then output Locations5; 
if number_of_addresses = 6  then output Locations6; 
if number_of_addresses = 7  then output Locations7; 
if number_of_addresses = 8  then output Locations8; 
if number_of_addresses = 9  then output Locations9; 
if number_of_addresses = 10 then output Locations10; 
run;

In this section, I'm calculating percentage based on the value of a variable.

data percent;
set data_all; 
if A <= 2 then percent_2 = num_employees / totals2; 
if A <= 3 then percent_3 = num_employees / totals3; 
if A <= 4 then percent_4 = num_employees / totals4; 
if A <= 5 then percent_5 = num_employees / totals5; 
if A <= 6 then percent_6 = num_employees / totals6; 
if A <= 7 then percent_7 = num_employees / totals7; 
if A <= 8 then percent_8 = num_employees / totals8; 
if A <= 9 then percent_9 = num_employees / totals9; 
if A <= 10 then percent_10 = num_employees / totals10; 
run;

Finally, here I'm just copying values from multiple variables into one variable based on the value of a variable.

data visit;
set data_all; 
if G = 2 then V = V_all2; 
if G = 3 then V = V_all3; 
if G = 4 then V = V_all4; 
if G = 5 then V = V_all5; 
if G = 6 then V = V_all6; 
if G = 7 then V = V_all7; 
if G = 8 then V = V_all8; 
if G = 9 then V = V_all9; 
if G = 10 then V = V_all10; 
run;

Thanks in advance!