@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-data-AKA-generate/ta-p/258712 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.
... View more
