SAS transpose with more then 100 variables

Question

I have dataset with 100 variables with prefix variable along with year 2013 to 2022. I want to transpose the data. Please find the sample below.

ID MODEL A_2013 A_2014 ....A_2022 B_2013 B_2014...B_2022 C_2013..C_2022
1   NEW   0.5    0.2   ... 0.8    0.2    0.3     0.9    0.2     0.5
2   OLD   0.2    0.9   .... 0.9   0.5    0.6      0.2    0.6     0.6
3   NEW   0.2    0.3   .... 0.2   0.6     0.9     0.2    0.6     0.1

I want to do like

    ID MODEL YEAR    A      B     C 
    1  NEW   2013   0.5    0.2    0.2  
    1  NEW   2014   0.2    0.5    0.8
    1  NEW   2022   0.8    0.2    0.5

how can i do it?

Answer 1

This can be done with one data step using arrays:

Data [Output] (keep=ID MODEL YEAR A B);

   set [Input];

   array arr_A{10} A_2013 - A_2022;
   array arr_B{10} B_2013 - B_2022;

   DO i=1 to 10;
      A=arr_A[i];
      B=arr_B[i];
      YEAR = 2012+i;
      OUTPUT;
   END;
RUN;

Answer 2

See following code which uses two PROC TRANSPOSE steps and 1 DATA STEP + 1 PROC SORT to acheive the output you are looking for. This could more probably be done in a single DATA step using ARRAYS. But, the following approach should be within reach of most SAS programmers.

/*sample data*/
data have;
INPUT ID MODEL $ A_2013 A_2014 A_2022 
B_2013 B_2014 B_2022
;
datalines;
1 NEW 0.5 0.2 0.1 0.4 3.4 12.89
2 OLD 0.5 0.2 0.3 0.4 3.4 12.5
3 NEW 0.5 0.2 0.1 0.2 3.4 12.4
4 OLD 0.5 0.2 4.1 0.4 3.4 12.0
5 NEW 0.2 0.1 0.3 0.4 3.1 12.1
6 OLD 2.5 1.2 2.1 1.4 2.4 1.2
;
run;
/*1st transpose: turn the input dataset from short and wide to long and thin*/
proc transpose data = have out = have_t(rename = (col1=value));
by id model;
run;

/*split the labels into two parts, segment & year*/
data have_t2;
length segment $8 year 8;
set have_t;
underScorePosition=index(_name_,'_');
segment = substr(_name_,1, underScorePosition-1);
year = input(substr(_name_,underScorePosition+1), 4.);
drop _name_;
run;
/*make sure values are properly sorted before passing to 2nd transpose*/
proc sort data = have_t2;
by id model year;
run;
/*2nd transpose: here turn  sgements into columns*/
proc transpose data = have_t2 out = want(drop =_name_);
by id model year;
id segment;
var value;
run;

Output:

ID  MODEL   year    A   B
1   NEW 2013    0.5 0.4
1   NEW 2014    0.2 3.4
1   NEW 2022    0.1 12.89
2   OLD 2013    0.5 0.4
2   OLD 2014    0.2 3.4
2   OLD 2022    0.3 12.5
3   NEW 2013    0.5 0.2
3   NEW 2014    0.2 3.4
3   NEW 2022    0.1 12.4
4   OLD 2013    0.5 0.4
4   OLD 2014    0.2 3.4
4   OLD 2022    4.1 12
5   NEW 2013    0.2 0.4
5   NEW 2014    0.1 3.1
5   NEW 2022    0.3 12.1
6   OLD 2013    2.5 1.4
6   OLD 2014    1.2 2.4
6   OLD 2022    2.1 1.2

Answer 3

You can achieve this quite easily in 2 passthroughs if you use a view for the variable renaming.

1 Proc Transpose

Transpose the data first in order to have only 4 columns:

1. The ID (id)
2. The model (model)
3. The label_year combination (NAME)
4. The value (COL1)

Note that i used an IF statement to eliminate all OLD models, since i did not see them in your desired output.

proc transpose data=DATAINPUT out=TRANSPOSEOUTPUT;
    where MODEL='NEW';
    by id model;
run;

2 Data Step View

Here we basically split NAME into label and year. In addition, some cleaning of the data (dropping NAME & renaming COL1)

data V_TRANSPOSEOUTPUT (drop=_NAME_ rename=(COL1=value)) /view=V_TRANSPOSEOUTPUT;
    length ID 8;
    length model $3;
    length year 8;
    length label $1;
    set TRANSPOSEOUTPUT;
    label=scan(_NAME_,1,'_');
    year=scan(_NAME_,2,'_');
run;

3 Proc Tabulate (instead of proc report)

I found proc tabulate more appropriate than proc report because - at least in my opninion - it is easier to construct a crosstab. At least, from your comments i derive you want to present the desired output as a report, not as a dataset.

proc tabulate data=V_TRANSPOSEOUTPUT;
    table id*model*year,label*value;
    class id model year year label;
    var value;
run;

I tested this and my output is slightly different from what you wrote, but i suspect that you made a couple of mistakes when writing out the results you desire. ;)