Proc report/tabulate to remove column name evaluating to .

[swwithsas]

I am new to sas and i could get to tabulate the below data and need the data formatted as in attachment. (ie; it populates the group variable in all the rows and removes the analysis column having . in its value. Could someone let me know if anything closer to this format is possible to achieve in sas?

                                    Balances on POU DTs 

Format   Format                  .     APR2018

name     value label      Balance Balance

TERM	-12 <= -6	.	.
	-120 <= -60	.	.
	-240 <= -120	.	.
	-360 <= -240	.	.
	-6 < 0	.	.
	<= -12	.	.
	1 <= 6	.	.
	12 <= 60	.	.
	120 <= 240	.	.
	240 <= 360	.	2532
	<= 480	.	.
	480 <= 600	.	.
	6 <= 12	.	.
	60 <= 120	.	.
	600 <= 720	.	.
	720 <= 840	.	.
	840 <= 960	.	.
	<= 0	.	-322
	>960	.	.

[PaigeMiller]

Can't you just remove that column name from the PROC REPORT or PROC TABULATE code?

[swwithsas]

No it cannot be removed as grouping is on date and on that group sum(balance_amount) is calculated.

[ballardw]

Provide some data and the Proc Tabulate or Report code you have attempted.

Following refers to Proc Tabulate.

By default Proc Tabulate does not display any CLASS values with missing results for all requested columns. You would have to use the options MISSIN and PRELOADFMT to come close to creating the output shown.

If you have a numeric variable you want to treat as a grouping variable as shown then use the variable as a CLASS variable with the format applied.

Tabulate also does not repeat row or column headers but spans across rows or columns with the same value.

I am not quite sure of your requirement though. You can't "remove a column" if you have used a value such as the dates for your "balances" if at least one date value exists.

You can use the Proc Tabulate option MISSTEXT=' ' or the SAS system option MISSING=' ' to display a blank instead of a dot.

[swwithsas]

proc report data=alldata colwidth=10 nowindows missing ;

column FMTNAME LABEL dt,balance;

define FMTNAME / group order=data;

define LABEL / display order = data;

define dt / across 'Balances on DTs';

define balance_amt / analysis sum ;

break after FMTNAME / skip;

Above is the code and data is a table containing FMTNAME LABEL dt,balance where dt field is '.' for FMTNAME LABEL combination as for that date this combination related data is not present and hence even the balance is '.' for the same.

[PaigeMiller]

Dates were not mentioned in your original description of the problem. Please explain further.

[swwithsas]

my source data is a table with data similar to below:

fmtname label  date        balance

x1       a1    20/03/2019   10

x1       a2    20/03/2019   20

x1       a3    .            .

x1       a4    25/03/2019   30

x1       a5    .            .

x2       b1    25/03/2019   40

x2       b2    .            .

x2       b3    20/03/2019   50

now i need a table as below:

                                        Balances on Dates

Format   Format           20/03/2019  25/03/2019

name     value label      Balance        Balance     

x1           a1                     10                 0

x1           a2                     20                 0

x1           a3                     0                   0

x1           a4                     0                  30

x1           a5                     0                   0

x2           b1                     0                   40

x2           b2                     0                   0

x2           b3                     50                 0

below was the code i used to generate such a table but as source data as "." in date and balance columns for some rows, I get that "." column also listed as below:

proc report data=alldata colwidth=10 nowindows missing ;

column FMTNAME LABEL dt,balance;

define FMTNAME / group order=data;

define LABEL / display order = data;

define dt / across 'Balances on Dates';

define balance / analysis sum ;

break after FMTNAME / skip;

                                        Balances on Dates

Format   Format           .             20/03/2019  25/03/2019

name     value label      Balance  Balance        Balance     

x1           a1                    .                10                 0

x1           a2                     .               20                 0

x1           a3                     .                0                   0

x1           a4                     .                0                  30

x1           a5                     .                0                   0

x2           b1                     .                0                   40

x2           b2                     .                0                   0

x2           b3                     .                50                 0

[swwithsas]

my source data is a table with data similar to below:

fmtname label  date        balance

x1       a1    20/03/2019   10

x1       a2    20/03/2019   20

x1       a3    .            .

x1       a4    25/03/2019   30

x1       a5    .            .

x2       b1    25/03/2019   40

x2       b2    .            .

x2       b3    20/03/2019   50

now i need a table as below:

                                        Balances on Dates

Format   Format           20/03/2019  25/03/2019

name     value label      Balance        Balance     

x1           a1                     10                 0

x1           a2                     20                 0

x1           a3                     0                   0

x1           a4                     0                  30

x1           a5                     0                   0

x2           b1                     0                   40

x2           b2                     0                   0

x2           b3                     50                 0

below was the code i used to generate such a table but as source data as "." in date and balance columns for some rows, I get that "." column also listed as below:

proc report data=alldata colwidth=10 nowindows missing ;

column FMTNAME LABEL dt,balance;

define FMTNAME / group order=data;

define LABEL / display order = data;

define dt / across 'Balances on Dates';

define balance / analysis sum ;

break after FMTNAME / skip;

                                        Balances on Dates

Format   Format           .             20/03/2019  25/03/2019

name     value label      Balance  Balance        Balance     

x1           a1                    .                10                 0

x1           a2                     .               20                 0

x1           a3                     .                0                   0

x1           a4                     .                0                  30

x1           a5                     .                0                   0

x2           b1                     .                0                   40

x2           b2                     .                0                   0

x2           b3                     .                50                 0

[swwithsas]

sorry in output table i am getting as of result of proc report does not have x1 populated for all a1,a2,a3,a4,a5 and similarly for x2. I need to do that as well.