topic Re: Proc report/tabulate to remove column name evaluating to . in SAS Programming

Proc report/tabulate to remove column name evaluating to .

swwithsas — Thu, 09 May 2019 15:38:07 GMT

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

Re: Proc report/tabulate to remove column name evaluating to .

PaigeMiller — Thu, 09 May 2019 17:56:38 GMT

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

Re: Proc report/tabulate to remove column name evaluating to .

ballardw — Thu, 09 May 2019 17:53:25 GMT

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.

Re: Proc report/tabulate to remove column name evaluating to .

swwithsas — Fri, 10 May 2019 07:55:07 GMT

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.

Re: Proc report/tabulate to remove column name evaluating to .

swwithsas — Fri, 10 May 2019 16:15:02 GMT

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

Re: Proc report/tabulate to remove column name evaluating to .

PaigeMiller — Fri, 10 May 2019 16:28:12 GMT

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

Re: Proc report/tabulate to remove column name evaluating to .

swwithsas — Sat, 11 May 2019 15:12:57 GMT

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

Re: Proc report/tabulate to remove column name evaluating to .

swwithsas — Sat, 11 May 2019 15:14:39 GMT

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.