Vary Row Numbers in a Loop?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

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.

 

Thanks in Advance!!

 


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

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; 




View solution in original post


All Replies
Super User
Super User
Posts: 7,401

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. Smiley Happy

Solution
‎03-30-2016 08:23 AM
Super User
Posts: 9,681

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! Smiley Happy Smiley Happy 

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. 

 

Thanks a Lot in Advance!!

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: 9,681

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 {Category} ;
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.

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

Discussion stats
  • 7 replies
  • 793 views
  • 3 likes
  • 3 in conversation