topic Re: PROC REPORT / TRANSPOSE in SAS Programming

PROC REPORT / TRANSPOSE

sasuser_8 — Thu, 03 Aug 2023 18:12:57 GMT

Hello,

I try to get the following result:

from the following table

DATA SALE;

INPUT ANNEE TYPE $ VAR1 VAR2 VAR3 VAR4;

CARDS;

2020 100 52 36 98 74
2020 200 36 52 2 6 
2021 100 36 55 44 7
2021 200 65 8 24 6
2022 100 6 36 54 8
2022 200 65 8 25 4
;

RUN;

I read the following post but it's not exactly the same case:

https://communities.sas.com/t5/SAS-Procedures/Using-proc-report-to-transpose-a-table/m-p/883261

Does anyone have de solution ?

Thank you

Re: PROC REPORT / TRANSPOSE

PaigeMiller — Thu, 03 Aug 2023 18:56:02 GMT

This should work:

proc transpose data=sale out=sale_t;
    by annee type;
    var var1-var4;
run;

proc report data=sale_t;
    columns _name_ annee,type,col1;
    define _name_/group ' ';
    define annee/across ' ';
    define type/across ' ';
    define col1/sum ' ';
run;
ods html close;

As a general rule, the wide data set you start with, where repeated values of a variable (in this case VAR) are in different columns, should be avoided. The desired formatting is in data set SALE_T, this is a long (not wide) data set, where repeated values of a variable are in different rows. Using long data sets will make your programming easier, as almost all SAS PROCs are designed to work with long data sets. Please keep this in mind in your future SAS work.

Re: PROC REPORT / TRANSPOSE

ballardw — Thu, 03 Aug 2023 18:41:10 GMT

Perhaps:

proc tabulate data=sale;
   class annee type;
   var var1-var4;
   table (var1-var4)*(max=' '*f=best5.),
         annee=' '*type=' '
         /row=float
   ;
run;

Proc Tabulate will allow different variables in a column. There might be an issue with what statistic you actually want given a larger data set. Sum might be appropriate as well.

The =' ' are to suppress default labels. The *f=best5.' after the MAX or other statistic is to set the format. Default with Tabulate would be to include 2 decimal places.

The table option "row=float is clean up the row labels where the statistic has been suppressed but would leave an apparently empty column otherwise.

Caution with Tabulate: Default behavior is that any observation with a missing value for any of the CLASS variables is to drop the entire observation. So if you have such you need to provide examples in the data and how you expect them to be handled.

@sasuser_8 wrote:

Hello,

I try to get the following result:

from the following table
DATA SALE;

INPUT ANNEE TYPE $ VAR1 VAR2 VAR3 VAR4;

CARDS;

2020 100 52 36 98 74
2020 200 36 52 2 6 
2021 100 36 55 44 7
2021 200 65 8 24 6
2022 100 6 36 54 8
2022 200 65 8 25 4
;

RUN;
I read the following post but it's not exactly the same case:

https://communities.sas.com/t5/SAS-Procedures/Using-proc-report-to-transpose-a-table/m-p/883261

Does anyone have de solution ?

Thank you

Re: PROC REPORT / TRANSPOSE

sasuser_8 — Thu, 03 Aug 2023 21:21:01 GMT

Ok it works as expected.

Thank you !