Dynamic Report with non fix number of columns and task to create automatic labels

[Ronein]

Hello

I wrote the process from start till end of creating dynamic summary report that create column labels automatically.

By the advice in this forum I understood that need to create long shape summary table and from it need to re-shape it to wide shape and then labels are created automatically.

I wonder if the process should be so long as it is in this example???

As I explained before the report will run with different periods (for example : In this run the periods are 2001,2004,2005  but in another run the periods can be changed for example to: 2001,2005,2006,2007)

Please note that  I wrote the code without running it because my SAS is not working now..

The task to create labels is important because columns names are in English but labels should be in my own language (Greek for example)

/*Step1-External Raw Data*/
Data Rawtbl2001;
Input CompanyID CustomerID amount;
Cards
888 1 10
888 1 20 
888 2 30
777 3 40
777 4 50
;
Run;

Data Rawtbl2004;
Input CompanyID CustomerID amount;
Cards
888 1 11
888 1 21 
777 4 51
;
Run;

Data Rawtbl2005;
Input CompanyID CustomerID amount;
Cards
888 1 12
888 1 23 
777 4 55
777 5 40
;
Run;

Data Raw_All;
SET Rawtbl2001(in=a)  Rawtbl2004(in=b) Rawtbl2005(in=c);
IF a then period=2001;
IF b then period=2004;
IF c then period=2005;
Run;

/**Step2-summary data set in Wide shape**/
proc sql;
create table Wide As
Select CompanyID,period,
         sum(amount) as amount,
        count(*) as Nr_Trans,
      count(distinct CustomerID) as Nr_Customers
From Raw_All
group by CompanyID,period
;
quit;

/*Step3-Change to Long shape*/
/*For each group of variables we must do it separately in proc transpose*/
proc transpose data=wide out=long1 prefix=amount;
by CompanyID notsorted;
var amount: ;
Run;

proc transpose data=wide out=long2 prefix=Nr_Trans;
by CompanyID notsorted;
var Nr_Trans : ;
Run;


proc transpose data=wide out=long3   prefix=Nr_Customers;
by CompanyID notsorted;
var Nr_Customers : ;
Run;

Data Long4 (rename=(_name_=field);
Merge Long1 (rename=(amount1=amount) drop=_name_)
           Long2 (rename=(Nr_Trans1=Nr_Trans) drop=_name_)
           Long3 (rename=(Nr_Customers1=Nr_Customers) );
period=substr(_name_,13);
Run;

/*Add a new column with desired labels */
Data Long5;
Set Long4;
IF substr(field,1,6)="amount" then wanted_label=catx('', "Transaction Amount",period);
IF substr(field,1,8)="Nr_Trans" then wanted_label=catx('', "Number of Transaction",period);
IF substr(field,1,12)="Nr_Customers" then wanted_label=catx('', "Number of Customers",period);
Run;

/*Step4-Create final summary Report with wide structure and labels that are created automatically using IDlabel statement*/

proc transpose data=Long5 out=wanted;
By CompanyID  notsorted;
ID field;
Idlabel wanted_label;
Var value;
Run;

[PaigeMiller]

So, as we discussed before in at least 2 previous threads, creating wide data sets is almost never necessary.

This string of long to wide to long to wide to long to long to long to wide just to be able to create a report is completely unnecessary.

PROC REPORT handles the dynamic nature of what you are trying to do, so you don't have to code it yourself. Starting with RAW_ALL, this does most of your work:

proc report data=raw_all;
    columns companyID period,amount period,amount=amount2;
    define companyID/group;
    define period/across;
    define amount/n "N Transactions";
    define amount2/sum "Sum Amount";
run;

I don't have to tell PROC REPORT what years I want columns for, it figures it out dynamically, so that if the data changes, and different years are in the data, the code doesn't have to change.

I leave it up to you to make any additional modifications you might want.

Plus, from now on, post code that works. Your first four data steps don't work. We really don't want to fix basic errors your code, when that ought to be something you do automatically without us asking.