proc report or proc tabulate to create a report of a sas dataset

[sq96]

I have a data set of the below format :

TotalValue	YOA	Level	Value	Ultimate
100	2000	First	23	67
200	2001	First	54	58
300	2002	First	567	34
100	2001	second	34	46
200	2002	second	56	97
300	2003	second	23	45
100	2001	third	54	32
200	2002	third	68	13
300	2003	third	45	98

I want to create a report of the below format :

Here Superheadings- First, Second and Third are merged heading of Value and Ultimate ( it's not showing properly below)

	First	Second	Third
Yoa	Value	Ultimate	Value	Ultimate	Value	Ultimate
2000	23	67	34	46	54	32
2001	54	58	56	97	68	13
2002	567	34	23	45	45	98

I tried using proc report and proc tabulate but I am unable to achieve the goal. Can anyone give me a hand in this?

Thanks

[Shmuel]

With proc tabulate:

proc tabulate data=have;
   class yoa level;
   var value ultimate;
   table yoa,            /* row category *,
         level*(value ultimate)*sum=*f=<format>
    ;
run;

[ballardw · Accepted solution]

---

@sq96 wrote:

I have a data set of the below format :

TotalValue	YOA	Level	Value	Ultimate
100	2000	First	23	67
200	2001	First	54	58
300	2002	First	567	34
100	2001	second	34	46
200	2002	second	56	97
300	2003	second	23	45
100	2001	third	54	32
200	2002	third	68	13
300	2003	third	45	98

I want to create a report of the below format :

Here Superheadings- First, Second and Third are merged heading of Value and Ultimate ( it's not showing properly below)

	First	Second	Third
Yoa	Value	Ultimate	Value	Ultimate	Value	Ultimate
2000	23	67	34	46	54	32
2001	54	58	56	97	68	13
2002	567	34	23	45	45	98

I tried using proc report and proc tabulate but I am unable to achieve the goal. Can anyone give me a hand in this?

Thanks

---

First thing, make sure that your example input and output data match. Your output implies that you have a YOA value of 2000 for Level-Second. Not in the input data. Similarly the values for YOA=2003 do not appear in the data but seem to be associated with different actual YOA values.

If you are coping from Excel then watch out for dragging things around and getting values incremented.

Best is to provide your data in the form a data step, test to make sure it generates the data needed.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against. Though this is likely confusing at this time.

This data step will allow you to create the desired output with proc tabulate:

data have;
  input TotalValue YOA Level $ Value Ultimate ;
datalines;
100 2000 First 23 67 
200 2001 First 54 58 
300 2002 First 567 34 
100 2000 second 34 46 
200 2001 second 56 97 
300 2002 second 23 45 
100 2000 third 54 32 
200 2001 third 68 13 
300 2002 third 45 98 
;

Note that the YOA values for level second and third have been changed to match the output.

For that output this slightly modified Proc Tabulate from @Shmuel will generate the desired output:

proc tabulate data=have;
   class yoa level;
   var value ultimate;
   table yoa,            /* row category */
         level=' '*(value ultimate)*sum=' '*f=best5.
    ;
run;

The comment was not quite proper for inline and the, Level=' ' and  sum=' '*f= needed a label or blank in quotes to suppress the labels for the variable and statistic from appearing.

And Proc report:

proc report data=have;
   column yoa level,(value Ultimate);
   define yoa/group;
   define level/across ' ';
run;

The quotes in the Define for Level suppress the heading text of Level that would appear by default.

If want the text of Second and Third use the Propcase function in the data step to regularize or enter the data as desired to start.