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

sq96 — Tue, 27 Aug 2019 04:43:29 GMT

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

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

Shmuel — Tue, 27 Aug 2019 04:47:43 GMT

With proc tabulate:

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

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

ballardw — Tue, 27 Aug 2019 15:03:26 GMT

@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.

topic Re: proc report or proc tabulate to create a report of a sas dataset in SAS Procedures

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

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

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