topic Re: Re-formatting a sas output using proc report in Statistical Procedures

Re-formatting a sas output using proc report

FerGui1 — Tue, 02 Nov 2021 15:12:33 GMT

Hello,

I have a SAS output like the one showing in table 1 and i would like to format it so that I have the two tables shown in tables 2 and 3. Any assistance on this will be greatly appreciated.

table 1

Shelf	Category	Article	Value
A	Office	Art1	54.566
A	Office	Art2	47.33333
Shared	Office	Art1	48.2
Shared	Office	Art2	50.18333
B	Office	Art1	54.9133
B	Office	Art2	51.646
A	Home	Art1	50.18
A	Home	Art2	46.11333
Shared	Home	Art1	54.913
Shared	Home	Art2	51.64667
B	Home	Art1	42.9
B	Home	Art2	49.96667

table2

Office
Article	Shared	A	B
Art1	48.2	54.6	54.9
Art2	50.2	47.3	51.6

table 3

Home
Article	Shared	A	B
Art1	54.9	50.2	42.9
Art2	51.6	46.1	50.0

Re: Re-formatting a sas output using proc report

PaigeMiller — Tue, 02 Nov 2021 15:26:56 GMT

UNTESTED CODE

proc report data=have;
    by category;
    columns article shelf,value;
    define article/group;
    define shelf/across;
    define value/sum format=best8.1;
run;

Re: Re-formatting a sas output using proc report

FerGui1 — Tue, 02 Nov 2021 16:34:46 GMT

Hi PaigeMiller,

Thank you so much for your assistance. I run your proposed coding and got the output below. I will still need to:

1. Sort the shelf columns so that it shows in the order Shared, A, and B

2. Replace the 'Value' row by the shelf headings (Shared, A, and B)

3. Eliminate the 'Shelf' row

4. Round all the values to 1 decimal place

Could you help on that?

Re: Re-formatting a sas output using proc report

PaigeMiller — Tue, 02 Nov 2021 16:49:03 GMT

At this point, I request that you provide your data as SAS data step code (instructions), rather than as a screen capture, so I can actually work with it.

Re: Re-formatting a sas output using proc report

FerGui1 — Tue, 02 Nov 2021 19:29:44 GMT

Hi,

Is this what you need?

data tryout1;
INPUT Shelf $ Category $ Article $ Value;
CARDS;
A Office Art1 54.566
A Office Art2 47.3333333
Shared Office Art1 48.2
Shared Office Art2 50.1833333
B Office Art1 54.9133
B Office Art2 51.646
A Home Art1 50.18
A Home Art2 46.1133333
Shared Home Art1 54.913
Shared Home Art2 51.6466667
B Home Art1 42.9
B Home Art2 49.9666667
;
proc sort data=tryout1; by category;
proc report data=tryout1;
by category;
columns article shelf,value;
define article/group;
define shelf/across;
define value/sum format=best8.1;
run;

Re: Re-formatting a sas output using proc report

PaigeMiller — Tue, 02 Nov 2021 19:39:32 GMT

data tryout2;
    set tryout1(where=(shelf='Shared')) tryout1(where=(shelf^='Shared'));
run;
proc sort data=tryout2;
    by category;
run;

proc report data=tryout2;
    by category;
    columns ("Article" article) (" " shelf),(" " value);
    define article/group ' ';
    define shelf/across order=data ' ';
    define value/sum format=8.1 ' ';
run;

Re: Re-formatting a sas output using proc report

FerGui1 — Tue, 02 Nov 2021 20:02:45 GMT

PaigeMiller,

It works just as I needed.

Thank you so much!