Importing Excel Data into SAS and converting tables out of matrix form

Question

I have the data below I'm inputting into SAS from Excel: 

I've tried running the following code: 

proc import datafile='C:\Users\jjsin\Desktop\PhD\STCR.xlsx' dbms=xlsx out=have replace;
getnames=no;
run;
proc transpose data=have(obs=2 drop=a) out=apple(drop=_:);
var _all_;
run;

data apple;
set apple;
length year $ 80;
retain year;
if not missing(col1) then year=col1;
drop col1;
run;

data have;
set have;
length state $ 2;
retain state;
if not missing(col1) then state=col1;
drop col1;
run;


proc transpose data=have(obs=1) out=banana;
var _all_;
run;
data label;
merge banana(firstobs=2) apple(in=inapple);
if inapple;
run;
proc sql noprint;
select _name_ into : names separated by ' ' from label;
select cats(_name_,'="', state,'_',year,'"') into : label separated by ' ' from label;
quit;
proc datasets library=work nodetails nolist;
modify have;
label &label ;
quit;
proc transpose data=have out=temp1;
by a notsorted;
var &names;
run;
data temp1;
set temp1;
id=scan(_label_,1,'_');
year=scan(_label_,-1,'_');
run;
data temp2;
set temp1(firstobs=25);
length a $ 2;
retain a;
rename a=state;
run;

proc transpose data=temp2 out=want(drop=_name_);
by state year col1 notsorted;
id id;
var col1;
run;

proc sort data=want; by year state col1; run;

I get a result with 1200 rows and 27 columns: the state abbreviations are in the 1st column--where they should be, but with many of them having only one letter. The years are showing as letters instead of numbers in the 2nd column. Then the data values--instead of all being in a 3rd column, are in the 3rd and also spread out between the 4th and 27th columns--and at least some of them are definitely out of order. I can't even verify them for sure, because I can't verify the state abbreviations. The total number of rows is correct at 1200--50 states * 24 years. But I want a result like this (minus the row numbers column):

Answer 1

Solved:

My final code:

data credo;
    input row @;
    input state $ @;
    do year=2019 to 1996 by -1;
       input stcred @;
       output;
    end;
    input; 
datalines;
1   AL  3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
2   AK  3   3   3   2   1   1   1   4   2   2   2   2   3   3   3   3   3   .   .   .   .   .   .   .
3   AZ  3   3   3   3   3   4   4   4   4   4   4   3   3   3   3   3   4   4   .   .   .   .   .   .
4   AR  3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
5   CA  4   4   4   4   4   6   6   7   7   7   6   5   5   5   6   6   9   6   5   5   5   5   5   5
6   CO  3   3   3   3   3   3   3   3   3   3   3   3   3   4   4   4   4   4   3   3   3   3   3   3
7   CT  6   6   5   4   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
8   DE  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
9   FL  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   2   2   2   2   2   2   2   2   2
10  GA  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
11  HI  2   2   2   2   3   3   3   3   3   3   3   3   3   4   4   4   4   4   4   4   4   4   4   4
12  ID  2   2   2   2   2   2   2   2   2   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
13  IL  10  10  10  9   7   7   7   6   5   5   5   3   3   3   3   3   3   3   3   3   3   3   3   3
14  IN  1   1   1   1   1   1   1   1   1   1   1   1   2   2   3   3   2   2   2   2   2   2   2   2
15  IA  1   1   1   1   1   1   1   1   1   1   1   1   2   2   2   2   2   2   2   2   2   2   2   2
16  KS  4   4   4   4   3   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2
17  KY  6   6   5   5   5   4   4   4   4   4   4   4   4   4   4   4   4   4   3   3   3   3   3   3
18  LA  4   4   4   3   3   3   3   3   3   4   4   5   6   6   6   5   5   6   6   6   6   6   6   6
19  ME  3   3   3   3   3   3   3   3   3   3   3   3   3   4   4   3   2   2   2   2   2   2   2   2
20  MD  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
21  MA  3   3   3   2   2   2   2   2   3   3   3   3   3   3   3   4   4   4   4   4   4   4   4   4
22  MI  3   3   4   4   4   4   4   4   4   4   4   4   4   3   3   2   2   1   1   1   1   1   1   1
23  MN  1   1   2   2   2   2   2   2   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
24  MS  3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
25  MO  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
26  MT  3   3   3   3   3   3   3   3   3   3   3   3   4   4   4   4   4   4   4   4   4   4   4   4
27  NE  1   1   1   1   1   1   1   1   1   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2
28  NV  2   3   3   3   3   3   3   3   3   2   2   2   2   2   3   3   3   3   3   3   3   3   3   3
29  NH  3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   2   2   2   2   2   2   2
30  NJ  7   7   7   6   6   5   4   4   4   3   3   3   3   3   3   4   3   3   2   2   2   2   2   2
31  NM  3   3   3   3   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2
32  NY  2   2   2   2   2   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
33  NC  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
34  ND  2   2   2   2   1   1   1   2   2   2   2   3   3   3   3   4   4   4   4   4   4   4   4   4
35  OH  2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2
36  OK  3   3   3   2   2   2   2   2   2   2   2   2   3   3   3   3   3   3   3   3   3   3   3   3
37  OR  2   2   2   2   2   2   2   2   2   3   3   3   3   4   4   4   4   3   3   3   3   3   3   3
38  PA  5   5   4   4   4   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   3
39  RI  3   3   3   3   3   3   3   3   3   3   3   3   3   3   3   4   4   4   4   4   4   4   4   4
40  SC  2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   1   1   1   1   1   1   1   1   1
41  SD  1   1   1   1   1   2   2   2   2   3   3   3   3   3   .   .   .   .   .   .   .   .   .   .
42  TN  1   1   1   1   2   2   2   2   2   2   2   2   2   2   3   3   3   3   3   3   3   3   3   3
43  TX  1   1   1   1   1   1   1   2   2   2   2   3   3   3   3   3   3   3   3   3   3   3   3   3
44  UT  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
45  VT  2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2   2
46  VA  1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1   1
47  WA  2   2   2   2   2   2   2   2   2   2   2   2   2   3   3   3   2   2   2   2   2   2   2   2
48  WV  4   4   4   4   3   3   3   3   3   3   3   4   4   4   4   4   4   4   4   4   4   4   4   4
49  WI  3   3   3   3   3   3   3   3   3   3   3   3   4   4   4   4   4   4   3   3   3   3   3   3
50  WY  2   2   2   1   1   1   1   1   1   2   2   2   3   3   3   3   3   3   3   3   3   3   3   3
;

proc sort data=credo out=credo;
 by year row;
 run;