topic Re: How can hide one of across column. in SAS Procedures

How can hide one of across column.

vimal — Tue, 27 Oct 2009 08:24:26 GMT

I am working on across function. In which if I take missing value it creates an unwanted column with none value . I don't want to display it in output.
I tried with.
DEFINE _C4_/ FORMAT=6.0 NOPRINT;
DEFINE _C5_/FORMAT=6.0 NOPRINT;

but it is not working .
Please explain me how can I get the desire output.

Re: How can hide one of across column.

Cynthia_sas — Tue, 27 Oct 2009 15:35:59 GMT

Hi:
It would be helpful to show ALL of the code that you're using and a small sample of your data. Are your variables in the INPUT dataset named _C4_ and _C5_??? Or, are you trying to use the NOPRINT option on the absolute column names that PROC REPORT creates internally??? Generally speaking, you do not use PROC REPORT's absolute column names in a DEFINE statement.

If, for example, your ACROSS variable was REGION and you had this column statement:
[pre]
COLUMN JOBCODE REGION,N;
DEFINE JOBCODE / GROUP;
DEFINE REGION / ACROSS;
[/pre]

If you had 4 regions (A, B, C, D), then the internal absolute column names (as calculated by PROC REPORT would be:
JOBCODE is column 1 on the report (not an ACROSS item)
count column for REGION A = _c2_
count column for REGION B = _c3_
count column for REGION C = _c4_
count column for REGION D = _c5_

On the other hand, if you had this column statement with 2 variables nested under REGION, it would be different:
[pre]
COLUMN JOBCODE REGION,(N SAL);
DEFINE JOBCODE / GROUP;
DEFINE REGION/ ACROSS;
DEFINE N / 'COUNT';
DEFINE SAL / Mean 'AVG SAL';
[/pre]

Then the absolute columns would be assigned differently:
JOBCODE would still be the first column.
_C2_ would be COUNT for REGION A
_C3_ would be SAL for REGION A
_C4_ would be COUNT for REGION B
_C5_ would be SAL for REGION B
_C6_ would be COUNT for REGION C
_C7_ would be SAL for REGION C
_C8_ would be COUNT for REGION D
_C9_ would be SAL for REGION D

And, if you were COMPUTING an item that was under an ACROSS variable, you would potentially have more absolute column names. But let's say, in the above example that you want to USE the N or count for some reason, but hide it on the report. Then in your DEFINE statement you would NOT use the absolute column names to hide the column, you would do this:

[pre]
COLUMN JOBCODE REGION,(N SAL);
DEFINE JOBCODE / GROUP;
DEFINE REGION/ ACROSS;
DEFINE N / 'COUNT' NOPRINT;
DEFINE SAL / Mean 'AVG SAL';
[/pre]

I'm not sure what you mean by "take missing values"??? Are you computing a value that comes out to missing or do you have an ACROSS report item with all missing values??? For example, with the above code snippet, just knowing the report item names isn't enough, it is also necessary to know whether there are observations with missing values for JOBCODE, missing values for REGION and/or missing values for the variables that are nested under REGION -- In order to figure the best approach.

This would be an instance where seeing a small sample or better description of your data would be useful. You might want to consider opening a track with Tech Support, as they can look at your actual data and ALL your code and help you arrive at a solution.

cynthia

Re: How can hide one of across column.

vimal — Wed, 28 Oct 2009 05:22:38 GMT

Dear,
Thanks for the reply. This is the small example for what i am looking for.
data test;
input Name $15. Type $15. Number 2.;
datalines;
NUNGAMBAKKAM .
PADIANALLUR All 20
RHROAD Non-Privilege 30
SELAIYUR .
PORUR Privilege 20
SANTHOME All 50
RHROAD Non-Privilege 60
CHENNA Privilege 25

;
run;

proc report data=test missing;
column name Type,Number;
define Name/group;
define Type/across;
define number/sum;
run;

Here, I do not want to display a column i.e with name 'Number' which do not contain any value along that I don't want to loose the Name which don't have any value
i.e. 'NUNGAMBAKKAM ' and 'SELAIYUR'.
now tall me what I have to do.

Re: How can hide one of across column.

data_null__ — Wed, 28 Oct 2009 13:21:58 GMT

[pre]
proc report nowd list data=test missing;
column name Type,N;
define Name/group;
define Type/across 'Type' '--';
define n / 'Number';
freq number;
run;
[/pre]

Re: How can hide one of across column.

Cynthia_sas — Wed, 28 Oct 2009 22:36:31 GMT

Hi:
I do not believe that the FREQ statement or NOPRINT will work for this situation. If I understand the original post correctly, this is the current output:
[pre]
******** first output described ??? ****************************
Non
All Privilege Privilege
Name Number Number Number Number
CHENNA . . . 25
NUNGAMBAKKAM . . . .
PADIANALLUR . 20 . .
PORUR . . . 20
RHROAD . . 90 .
SANTHOME . 50 . .
SELAIYUR . . . .
[/pre]

I think it is the NUMBER column of all missing that is not desired on the report.

Without the MISSING option or with MISSING option and N and the FREQ statement, the results would be this:
[pre]
Code:
proc report data=test missing nowd nocenter
split='-' ;
column name Type,N ;
define Name/group ;
define Type/across width=10;
freq number;
run;

proc report data=test nowd nocenter
split='-' out=work.repout;
column name Type,Number;
define Name/group ;
define Type/across width=10;
define number/sum 'Number';
run;

************** RESULTS ***********************
type
Non
All Privilege Privilege
Name N N N
CHENNA . . 25
PADIANALLUR 20 . .
PORUR . . 20
RHROAD . 90 .
SANTHOME 50 . .

[/pre]

The only difference between the 2 outputs is whether you see N or Number in the header underneath TYPE. Neither of these methods puts the names for NUNGAMBAKKAM and SELAIYUR on the report. In order to get NUNGAMBAKKAM and SELAIYUR as rows on the report, you have to turn on the MISSING option. And, the side effect of the MISSING option is that while you get the names you want, the NUMBER column is NOT as you want -- but MISSING forces REPORT to display a blank value for TYPE (as shown in the first undesired report shown above at the very top)

If I understand correctly THIS (below) is the desired report output?? With the list of names as shown on the first output snapshot, but WITHOUT the missing column when TYPE=' '???
[pre]
Non
All Privilege Privilege
Name Number Number Number
CHENNA . . 25
NUNGAMBAKKAM . . .
PADIANALLUR 20 . .
PORUR . . 20
RHROAD . 90 .
SANTHOME 50 . .
SELAIYUR . . .
[/pre]

Two possible workarounds are
1) make two passes through the output -- In the first pass, create one summary dataset and then run PROC REPORT on the summarized dataset or
2) use PROC REPORT, COMPLETEROWS and PRELOADFMT to provide the list of names that you want to see on the report. This approach could be cumbersome if the list of names is long.

cynthia

Re: How can hide one of across column.

data_null__ — Thu, 29 Oct 2009 15:07:15 GMT

I see now, the OP wants to keep the rows with all zeros(missing) and get rid of any columns with all zeros. As you say using PRELOADFMT is one way to achieve this result. Even it the format would have many levels it is easy to create from the original data.

I believe this produces the desired result.

[pre]
data test;
infile cards missover;
input Name :$15. Type :$15. Number 2.;
datalines;
NUNGAMBAKKAM .
PADIANALLUR All 20
RHROAD Non-Privilege 30
SELAIYUR .
PORUR Privilege 20
SANTHOME All 50
RHROAD Non-Privilege 60
CHENNA Privilege 25
;;;;
run;
proc sort data=test(keep=name) nodupkey out=names;
by name;
run;
data names;
retain fmtname 'name' type 'C';
set names;
start = name;
label = name;
run;
proc format cntlin=names;
select $name;
run;

proc report nowd list data=test completerows;
column name Type, Number;
define Name / group format=$name. preloadfmt;
define Type / across 'Type' '--';
define number / sum 'Number';
run;
[/pre]