## Vary Row Numbers in a Loop?

Solved
Occasional Contributor
Posts: 10

# Vary Row Numbers in a Loop?

Hi,

I have a NXN dataset from which i would like to have a one row (1XN) output. I need to calculate percentages in each column. As I move along the column the Numerator and Denominator keep on reducing from 1 row below.

Data I Have:

Category Month 0 1 2 3 4 5 6 7 8 9
Categ_1 Month_1 15 15 15 14 14 13 13 13 13 13
Categ_1 Month_2 8 8 8 7 6 5 5 5 5 5
Categ_1 Month_3 15 15 14 14 14 12 11 11 11 10
Categ_1 Month_4 17 17 17 15 14 14 13 12 11 11
Categ_1 Month_5 25 25 25 23 21 19 18 16 16 16
Categ_1 Month_6 21 21 18 17 14 13 12 11 11 8
Categ_1 Month_7 29 28 25 23 20 18 15 14 13 13
Categ_1 Month_8 32 31 30 28 25 23 21 20 18 17
Categ_1 Month_9 38 37 35 32 27 24 23 20 17 16
Categ_1 Month_10 30 30 28 27 24 21 20 18 17 17

What I want is

Categ_1 100% 99% 94% 87% 82% 79% 76% 76% 78% 87%

where for column 1 (i.e. 100%) = (total in 0th month)/(total in 0th month)

where for column 2 (i.e. 99%) = (total in 1th month-last row)/(total in 0th month-last row) and so on...

I have done it in excel using a simple OFFSET formula. It would be great if anyone can help me code it in SAS. I am finding it difficult to loop accross column while varying the rows to consider vary as well.

Attached is the excel with formula to clarify what i am trying out here.

Accepted Solutions
Solution
‎03-30-2016 08:23 AM
Super User
Posts: 10,761

## Re: Vary Row Numbers in a Loop?

```It is a piece of cake for IML.
You can run it unders SAS University Edition.

data have;
input Category \$ Month \$ _0 _1 _2 _3 _4 _5 _6 _7 _8 _9;
cards;
Categ_1 Month_1 15 15 15 14 14 13 13 13 13 13
Categ_1 Month_2 8 8 8 7 6 5 5 5 5 5
Categ_1 Month_3 15 15 14 14 14 12 11 11 11 10
Categ_1 Month_4 17 17 17 15 14 14 13 12 11 11
Categ_1 Month_5 25 25 25 23 21 19 18 16 16 16
Categ_1 Month_6 21 21 18 17 14 13 12 11 11 8
Categ_1 Month_7 29 28 25 23 20 18 15 14 13 13
Categ_1 Month_8 32 31 30 28 25 23 21 20 18 17
Categ_1 Month_9 38 37 35 32 27 24 23 20 17 16
Categ_1 Month_10 30 30 28 27 24 21 20 18 17 17
;
run;
proc iml;
use have;
read point 1 var {Category} ;
read all var _num_ into x[c=vname];
close;

want=j(1,ncol(x),.);
do i=1 to ncol(x);
want[i]=x[1:nrow(x)-i+1,i][+]/x[1:nrow(x)-i+1,1][+] ;
end;

mattrib want format=percent8.2;
create want from want[r=Category c=vname];
append from want[r=Category];
close;
quit;

```

All Replies
Super User
Posts: 9,599

## Re: Vary Row Numbers in a Loop?

Hi,

Something like this:

```data have;
category="Categ_1"; month="Month_1"; _0=15; _1=15; _2=15; _3=14; _4=14; output;
category="Categ_1"; month="Month_2"; _0=8; _1=8; _2=8; _3=7; _4=6; output;
run;
data inter;
set have;
array res{5} _0--_4;
month_res=sum(of res{*});
run;
proc transpose data=inter out=want;
by category;
var month_res;
id month;
run;
data want (drop=i);
set want (drop=_name_);
array month_{2};
array pcent{2};
do i=1 to 2;
pcent{i}=(month_{i}/month_{1}*100);
end;
run;```
Occasional Contributor
Posts: 10

## Re: Vary Row Numbers in a Loop?

Thanks a lot for your response

I think it is moving around a column and not varying the row numbers across the columns.

I may transpose it up and try around it to get the numbers.

Solution
‎03-30-2016 08:23 AM
Super User
Posts: 10,761

## Re: Vary Row Numbers in a Loop?

```It is a piece of cake for IML.
You can run it unders SAS University Edition.

data have;
input Category \$ Month \$ _0 _1 _2 _3 _4 _5 _6 _7 _8 _9;
cards;
Categ_1 Month_1 15 15 15 14 14 13 13 13 13 13
Categ_1 Month_2 8 8 8 7 6 5 5 5 5 5
Categ_1 Month_3 15 15 14 14 14 12 11 11 11 10
Categ_1 Month_4 17 17 17 15 14 14 13 12 11 11
Categ_1 Month_5 25 25 25 23 21 19 18 16 16 16
Categ_1 Month_6 21 21 18 17 14 13 12 11 11 8
Categ_1 Month_7 29 28 25 23 20 18 15 14 13 13
Categ_1 Month_8 32 31 30 28 25 23 21 20 18 17
Categ_1 Month_9 38 37 35 32 27 24 23 20 17 16
Categ_1 Month_10 30 30 28 27 24 21 20 18 17 17
;
run;
proc iml;
use have;
read point 1 var {Category} ;
read all var _num_ into x[c=vname];
close;

want=j(1,ncol(x),.);
do i=1 to ncol(x);
want[i]=x[1:nrow(x)-i+1,i][+]/x[1:nrow(x)-i+1,1][+] ;
end;

mattrib want format=percent8.2;
create want from want[r=Category c=vname];
append from want[r=Category];
close;
quit;

```
Occasional Contributor
Posts: 10

## Re: Vary Row Numbers in a Loop?

Awesome!! I was also looking for an application of proc IML. I will test this out and let you know if any issues!!!

AWESOME!

Occasional Contributor
Posts: 10

## Re: Vary Row Numbers in a Loop?

Hi KSharp,

Given that i am pretty new to IML, can you guide me on how to make it a bit more dynamic so as it runs through all the Categories I have and append the vecotors created for all the categories returning a Category level dataset.

Occasional Contributor
Posts: 10

## Re: Vary Row Numbers in a Loop?

something like this

Categories _0 _1 _2 _3 _4 _5 _6 _7 _8 _9 _10
Categ_1 100% 87% 79% 70% 61% 54% 46% 42% 36% 31% 28%
Categ_2 100% 84% 79% 67% 56% 48% 38% 36% 33% 29% 33%
Categ_3 100% 77% 67% 61% 56% 50% 53% 48% 44% 35%
Categ_4 100% 71% 68% 57% 47% 38%
Categ_5 100% 80% 64% 54% 44% 37% 31% 27% 28% 29% 28%

Super User
Posts: 10,761

## Re: Vary Row Numbers in a Loop?

```Sure.

data have;
input Category \$ Month \$ _0 _1 _2 _3 _4 _5 _6 _7 _8 _9;
cards;
Categ_1 Month_1 15 15 15 14 14 13 13 13 13 13
Categ_1 Month_2 8 8 8 7 6 5 5 5 5 5
Categ_1 Month_3 15 15 14 14 14 12 11 11 11 10
Categ_1 Month_4 17 17 17 15 14 14 13 12 11 11
Categ_1 Month_5 25 25 25 23 21 19 18 16 16 16
Categ_1 Month_6 21 21 18 17 14 13 12 11 11 8
Categ_1 Month_7 29 28 25 23 20 18 15 14 13 13
Categ_1 Month_8 32 31 30 28 25 23 21 20 18 17
Categ_1 Month_9 38 37 35 32 27 24 23 20 17 16
Categ_1 Month_10 30 30 28 27 24 21 20 18 17 17
Categ_2 Month_1 15 15 15 14 14 13 13 13 13 13
Categ_2 Month_2 8 8 8 7 6 5 5 5 5 5
Categ_2 Month_3 15 15 14 14 14 12 11 11 11 10
Categ_2 Month_4 17 17 17 15 14 14 13 12 11 11
Categ_2 Month_5 25 25 25 23 21 19 18 16 16 16
Categ_2 Month_6 21 21 18 17 14 13 12 11 11 8
Categ_2 Month_7 29 28 25 23 20 18 15 14 13 13
Categ_2 Month_8 32 31 30 28 25 23 21 20 18 17
Categ_2 Month_9 38 37 35 32 27 24 23 20 17 16
Categ_2 Month_10 30 30 28 27 24 21 20 18 17 17
;
run;
proc iml;
use have nobs nobs;
read all var _num_ into x[c=vname];
close;

start_end=t(loc(Category^=t({' '}||remove(Category,nobs))))||
t(loc(Category^=t(remove(Category,1)||{' '})));

want=j(nrow(start_end),ncol(x),.);
do j=1 to nrow(start_end);
do i=1 to ncol(x);
want[j,i]=x[start_end[j,1]:start_end[j,2]-i+1,i][+]/
x[start_end[j,1]:start_end[j,2]-i+1,1][+] ;
end;
end;

cat=Category[start_end[,1]];
mattrib want format=percent8.2;
create want from want[r=cat c=vname];
append from want[r=cat ];
close;
quit;

```
🔒 This topic is solved and locked.