How to do trasition matrix of data

[y658li]

Hello,

I have a table looks like this:

Segment	actual_year	actual_quart	q1	q2	q3
I-1	2020	Q2	1	2	3
I-2	2020	Q2	4	5	6
I-3	2020	Q2	7	8	9

So I want to know how to transform the table and make it looks like this:

Segment	scenario date	number
I-1	2020Q3	1
I-2	2020Q3	4
I-3	2020Q3	7
I-1	2020Q4	2
I-2	2020Q4	5
I-3	2020Q4	8
I-1	2021Q1	3
I-2	2021Q1	6
I-3	2021Q1	9

My question is, how to fix the column A (segment), but keep rotating the column D E F ...... (till the end of column Z) ? In addition, I also have an indicator which 2020Q2, each time I rotate the matrix, I need to add 1 quarter in the scenario date column. For example, the first scenario date is 2020Q3 and in row 4, it changes to 2020Q4...

Thank you very much!

Lydia

[novinosrin · Accepted solution]

data have;
input Segment $	actual_year	actual_quart :$2.	q1	q2	q3;
cards;
I-1	2020	Q2	1	2	3
I-2	2020	Q2	4	5	6
I-3	2020	Q2	7	8	9
;

data want;
 if 0 then set have(keep=segment);
 scenario_date=.;
 set have;
 _n_=input(cats(actual_year,actual_quart),yyq6.);
 array t q1-q3;
 do n=1 to dim(t);
  scenario_date=intnx('qtr',_n_,n);
  number=t(n);
  output;
 end;
 drop q1-q3 actual_year actual_quart;
 format scenario_date yyq6.;
run;
  
proc sort data=want out=final_want(drop=n);
 by n;
run;

proc print  noobs data=final_want;run;

[y658li]

Here is the error message:

do n=1 to dim(t);

_

133

ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.

32 number=t(n);

_

68

ERROR 124-185: The variable t has already been defined.

ERROR 133-185: A loop variable cannot be an array name or a character variable; It must be a scalar numeric.

ERROR 68-185: The function T is unknown, or cannot be accessed.

[novinosrin]

Hi @y658li  Are you sure you posted a "representative mock sample" of your real? Are you cognizant of the variables in the dataset. Here is the log of the test results of the code I posted-

data have;
input Segment $	actual_year	actual_quart :$2.	q1	q2	q3;
cards;
I-1	2020	Q2	1	2	3
I-2	2020	Q2	4	5	6
I-3	2020	Q2	7	8	9
;

data want;
 if 0 then set have(keep=segment);
 scenario_date=.;
 set have;
 _n_=input(cats(actual_year,actual_quart),yyq6.);
 array t q1-q3;
 do n=1 to dim(t);
  scenario_date=intnx('qtr',_n_,n);
  number=t(n);
  output;
 end;
 drop q1-q3 actual_year actual_quart;
 format scenario_date yyq6.;
run;
  
proc sort data=want out=final_want(drop=n);
 by n;
run;

proc print  noobs data=final_want;run;

LOG:

7176  data have;
7177  input Segment $ actual_year actual_quart :$2.   q1  q2  q3;
7178  cards;

NOTE: The data set WORK.HAVE has 3 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


7182  ;
7183
7184  data want;
7185   if 0 then set have(keep=segment);
7186   scenario_date=.;
7187   set have;
7188   _n_=input(cats(actual_year,actual_quart),yyq6.);
7189   array t q1-q3;
7190   do n=1 to dim(t);
7191    scenario_date=intnx('qtr',_n_,n);
7192    number=t(n);
7193    output;
7194   end;
7195   drop q1-q3 actual_year actual_quart;
7196   format scenario_date yyq6.;
7197  run;

NOTE: There were 3 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 9 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.03 seconds


7198
7199  proc sort data=want out=final_want(drop=n);
7200   by n;
7201  run;

NOTE: There were 9 observations read from the data set WORK.WANT.
NOTE: The data set WORK.FINAL_WANT has 9 observations and 3 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


7202
7203  proc print  noobs data=final_want;run;

NOTE: There were 9 observations read from the data set WORK.FINAL_WANT.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.09 seconds
      cpu time            0.00 seconds

OUTPUT;

Segment	scenario_date	number
I-1	2020Q3	1
I-2	2020Q3	4
I-3	2020Q3	7
I-1	2020Q4	2
I-2	2020Q4	5
I-3	2020Q4	8
I-1	2021Q1	3
I-2	2021Q1	6
I-3	2021Q1	9

[ballardw]

---

@y658li wrote:

Here is the error message:

do n=1 to dim(t);

_

133

ERROR: The DIM, LBOUND, and HBOUND functions require an array name for the first argument.

32 number=t(n);

_

68

ERROR 124-185: The variable t has already been defined.

ERROR 133-185: A loop variable cannot be an array name or a character variable; It must be a scalar numeric.

ERROR 68-185: The function T is unknown, or cannot be accessed.

---

When you get an error include the entire data step or proc code with ALL of the code and messages. Copy from the log and paste into a code box opened on the forum with the </> icon so the diagnostic character positions do not get moved and actually are useful.

The error from the DIM function means that you did not include an ARRAY definition for the array T prior to use, or attempted to use an array name that already has a variable of that name in the data.

[y658li]

ERROR 133-185: A loop variable cannot be an array name or a character variable; It must be a scalar numeric.

Hi this is my code:

data new_table;

if 0 then set CORP_CCAR_RESUBMIT(keep=Segment);

scenario_date=.;

set CORP_CCAR_RESUBMIT;

_n_=input(cats(actual_year,actual_quarter),yyq6.);

array time Q1-Q20;

do i=1 to dim(time);

scenario_date=intnx('qtr',_n_,i);

number=time(i);

output;

end;

drop Q1-Q20 actual_year actual_quarter;

format scenario_date yyq6.;

run;

and this is my log:

127 do i=1 to dim(time);

_

133

NOTE: The array time has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as array references and not function references.

ERROR 133-185: A loop variable cannot be an array name or a character variable; It must be a scalar numeric.

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

127:7

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

128:33 129:15

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.NEW_TABLE may be incomplete. When this step was stopped there were 0 observations and 26 variables.

WARNING: Data set WORK.NEW_TABLE was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 435.40k

OS Memory 28844.00k

Timestamp 2020-09-18 01:43:05 PM

Step Count 641 Switch Count 0

Page Faults 0

Page Reclaims 13

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

[ballardw]

---

@y658li wrote:

ERROR 133-185: A loop variable cannot be an array name or a character variable; It must be a scalar numeric.

Hi this is my code:

data new_table;

if 0 then set CORP_CCAR_RESUBMIT(keep=Segment);

scenario_date=.;

set CORP_CCAR_RESUBMIT;

_n_=input(cats(actual_year,actual_quarter),yyq6.);

array time Q1-Q20;

do i=1 to dim(time);

scenario_date=intnx('qtr',_n_,i);

number=time(i);

output;

end;

drop Q1-Q20 actual_year actual_quarter;

format scenario_date yyq6.;

run;

and this is my log:

127 do i=1 to dim(time);

_

133

NOTE: The array time has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as array references and not function references.

ERROR 133-185: A loop variable cannot be an array name or a character variable; It must be a scalar numeric.

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

127:7

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

128:33 129:15

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.NEW_TABLE may be incomplete. When this step was stopped there were 0 observations and 26 variables.

WARNING: Data set WORK.NEW_TABLE was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 435.40k

OS Memory 28844.00k

Timestamp 2020-09-18 01:43:05 PM

Step Count 641 Switch Count 0

Page Faults 0

Page Reclaims 13

Page Swaps 0

Voluntary Context Switches 0

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

---

Since your LOG does not start with "data new_table" then either your LOG entry is incomplete or you did not submit these lines:

data new_table;

if 0 then set CORP_CCAR_RESUBMIT(keep=Segment);

scenario_date=.;

set CORP_CCAR_RESUBMIT;

_n_=input(cats(actual_year,actual_quarter),yyq6.);

array time Q1-Q20;

Since you did not post the log into a code box as suggested then these lines:

127 do i=1 to dim(time);

_

133

NOTE: The array time has the same name as a SAS-supplied or user-defined function. Parentheses following this name are treated as array references and not function references.

ERROR 133-185: A loop variable cannot be an array name or a character variable; It must be a scalar numeric.

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

127:7

Cannot be used to diagnose as intended. One critical piece of information here is the note about Numeric values converted to character at position 7 on line 127. That is what the 127:7 means.

You show

127 do i=1 to dim(time);

I have a very strong suspicion that you have a variable named I in your data set that is character. So the number 1 is being converted to '1' for the loop. Which means the "to" range is not allowed. When character values are used as loop controls they have to be listed and comma separated. However since the way you posted the incomplete log it is not possible to actual determine that 1 is actually in column 7 or not.

You have notes

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

128:33 129:15

Which indicates there are probably problems with lines 128 and 129. But those lines are not included.

By the entire data step, in the log, start copying at "data " and copy everything through the last note or warning.

And paste in the forum into a code box opened using the </> icon. You can see from this post what the results look like.

Here is part of log pasted as I request:

1    data junk;
2       set sashelp.class;
3       array j _numeric_;
4       do i = '1' to dim(j);
5          x=j[i]*3;
6          put x=;
7       end;
8    run;

NOTE: Character values have been converted to numeric
      values at the places given by: (Line):(Column).
      4:11

Here is the same thing pasted in the main message window:

1 data junk;
2 set sashelp.class;
3 array j _numeric_;
4 do i = '1' to dim(j);
5 x=j[i]*3;
6 put x=;
7 end;
8 run;

NOTE: Character values have been converted to numeric
values at the places given by: (Line):(Column).
4:11

In the code box I can count characters and tell that line 4:11 means the (obvious) character conversion. Without the code box you can't tell.