Remember that the transposed data set, Work.Ctarget2, needs to be merged with Sasuser.Monthsum by the values of Year and Month. Neither data set is currently structured correctly for the merge.

In order to correctly structure Work.Ctarget2 for the merge, a BY statement needs to be used with PROC TRANSPOSE. For each BY group, PROC TRANSPOSE creates one observation for each variable that it transposes. The BY variable itself is not transposed.

The following program transposes Sasuser.Ctargets by the value of Year. The resulting output data set, Work.Ctarget2, now contains 12 observations for each year (1997, 1998, and 1999).

proc transpose data=sasuser.ctargets 
     out=work.ctarget2
     name=Month
     prefix=Ctarget;
     by year;
run;

The last step in preparing Work.Ctarget2 for the merge is to use the SORT procedure to sort the data set by Year and Month as shown in the following program:

proc sort data=work.ctarget2;
   by year month;
run; 

Notice that in the sorted version of Work.Ctarget2, the values of month are sorted alphabetically within year.

The data in Sasuser.Monthsum must also be reorganized for the merge because the month and year values in that data set are combined in the variable SaleMon.

The following program creates two new variables, Year and Month, to hold the year and month values. The values for Year are created from SaleMon using the INPUT and SUBSTR functions. The values for Month are extracted from SaleMon using the LOWCASE and SUBSTR functions.

data work.mnthsum2;
   set sasuser.monthsum(keep=SaleMon RevCargo);
   length Month $ 3;
   Year=input(substr(SaleMon,4),4.);
   Month=propcase(SaleMon);
 run;

As with Work.Ctarget2, the last step in preparing for the merge is to sort the data set by the values of Year and Month as shown in the following program:

proc sort data=work.mnthsum2;
   by year month;
run;

Notice that in the sorted version of Work.Mnthsum2, the values of month are sorted alphabetically within year.

When the data is structured correctly, Work.Mnthsum2 and Work.Ctarget2 can be merged by the values of Year and Month as shown in the following program:

data work.merged;
   merge work.mnthsum2 work.ctarget2;
   by year month;
run;

PROC PRINT output shows the resulting data set Work.Merged. The values of RevCargo represent the actual cargo revenue for each month. The values of Ctarget1 represent the target cargo values for each month.

proc print 
     data=work.merged (obs=10);
   format ctarget1 dollar15.2;
   var month year revcargo ctarget1;
run;