SAS, proc transpose without blank spaces

September 2, 2022

I’ve got some data in the form:

file_date  | segment |grade | volume
30Jun2017  |   1     |  A0  |  1000
31Jul2017  |   1     |  A0  |  542
31Aug2017  |   1     |  A0  |  4863
.
.

I want to transpose this so that it’s in the form:

grade | segment | 30Jun2017 | 31Jul2017 | 31Aug2017 | etc.
  A0  |    1    |    1000   |    542    |   4863    |

I’ve tried this using

proc transpose data=data1 out=data1_trans;
     by grade NOTSORTED;
     id file_date;
     var volume;
run;

I was going to do another proc transpose with the segment as the var and then merge them, but the output of this results in a bunch of blank spaces like:

grade | _NAME_ | 30Jun2017| 31Jul2017 |
  A0  | volume |   1000  |         .  |
  A1  | volume |    56   |         .  |
  A2  | volume |    884  |         .  |
  A0  | volume |      .  |       542  |
  A1  | volume |      .  |       353  |
  A2  | volume |      .  |        45  |

Is there anyway to get rid of those cells that are blank and have all of the data next to each other?

>Solution :

First, sort your data by grade and segment, then run proc transpose by grade segment. You need to include segment as a by variable for it to be included in your output.

proc sort data=data1;
    by grade segment;
run;

proc transpose data=data1 out=data1_trans;
     by grade segment;
     id file_date;
     var volume;
run;