Hi,

I am trying to use COMPLTEROWS option to show all possible subjects on my
report. the across variable is gender with two levels. the variable share
the same coumn with the GENDER is character type. it is defined
as "DISPLAY" in define statment. the problem i am having is I get too many
unwanted rows. Based on SAS DOC :

"displays all possible combinations of the values of the group variables,
even if one or more of the combinations do not occur in the input data
set. Consequently, the row headings are the same for all logical pages of
the report within a single BY group."

I shouldn't get that many rows(each subjects have 37 rows). Can someone
explain why I am having 37 rows for each subject.

Thank you.


The code below will demonstrate my problem. when you run through the code,
please check the sas dataset OUT to see # of the OBS per subject.




data class;
retain fmtname 'nmlst' type 'c';
set sashelp.class (keep=name) end=last;
start=name;
label=name;
output;
if last then do;
name='Yu';
start=name;
label=name;
output;
end;
run;


proc format cntlin=class;
run;

proc report data=sashelp.class nowd Completerows out=out;
column name sex,(weight height) dummy;
define name/group preloadfmt order=data format=$nmlst. ;
define sex/across;
define weight/display;
define height/display;
define dummy/computed;
run;

Hiya

I believe COMPLETEROWS will give you 1 column for each value of Height in
your input data. Likewise for Weight.

By changing your code to

data class;
retain dummy 0;
set sashelp.class (keep=name sex weight height) end=last;
output;
if last then do;
name='Yu';
output;
end;
run;

proc report data=class nowd out=out;
column name sex,(weight height) dummy;
define name/group order=data;
define sex/across;
define weight/display width=6;
define height/display width=6;
define dummy/sum noprint;  /* Dont want this printed */
quit;  /* Changed from RUN  */


I achieved results that I think you want.

There is no need to specify preloadfmt or format on your NAME statement.
I only use preloadfmt for ACROSS variables.
I specified NOPRINT on the define statement, as I don't think you need
this listed.

You may need to create a format for GENDER.

Hope this helps.

Tom

On Thu, 16 Aug 2007 10:37:24 -0400, Yu Zhang <zhangyu05@GMAIL.COM> wrote:

>Hi,
>
>I am trying to use COMPLTEROWS option to show all possible subjects on my
>report. the across variable is gender with two levels. the variable share
>the same coumn with the GENDER is character type. it is defined
>as "DISPLAY" in define statment. the problem i am having is I get too many
>unwanted rows. Based on SAS DOC :
>
>"displays all possible combinations of the values of the group variables,
>even if one or more of the combinations do not occur in the input data
>set. Consequently, the row headings are the same for all logical pages of
>the report within a single BY group."
>
>I shouldn't get that many rows(each subjects have 37 rows). Can someone
>explain why I am having 37 rows for each subject.
>
>Thank you.
>
>
>The code below will demonstrate my problem. when you run through the code,
>please check the sas dataset OUT to see # of the OBS per subject.
>
>
>
>
>data class;
>retain fmtname 'nmlst' type 'c';
>set sashelp.class (keep=name) end=last;
>start=name;
>label=name;
>output;
>if last then do;
>name='Yu';
>start=name;
>label=name;
>output;
>end;
>run;
>
>
>proc format cntlin=class;
>run;
>
>proc report data=sashelp.class nowd Completerows out=out;
>column name sex,(weight height) dummy;
>define name/group preloadfmt order=data format=$nmlst. ;
>define sex/across;
>define weight/display;
>define height/display;
>define dummy/computed;
>run;

Hi, Tom,

thank you for taking time looking at my problem and your suggestion. I
think I didn't make my question clear. and sorry I can provide the real
sample to list.

I have a serum test dataset with patno,sample_date,result, visit_day and
regimen. not evey subject in the study was drawn the blood sample. I need to
list all subjects in my report, even a subject doesn't has test result. the
regimen and visit_day will be defined as ACROSS varaible.

I knew I can make a full list of subjects and merge it with my test dataset
to get the desired output. However, I believe the completerows option can
let me have same output but with less coding.

My question is why Completerows gives out so many unwanted rows and how?
What this option does behind the scene?

The sample code can be used to generate the strange output dataset.

any explanations??



On 8/17/07, Tom Hide <tom.hide@dehiworld.com> wrote:
>
> Hiya
>
> I believe COMPLETEROWS will give you 1 column for each value of Height in
> your input data. Likewise for Weight.
>
> By changing your code to
>
> data class;
> retain dummy 0;
> set sashelp.class (keep=name sex weight height) end=last;
> output;
> if last then do;
> name='Yu';
> output;
> end;
> run;
>
> proc report data=class nowd out=out;
> column name sex,(weight height) dummy;
> define name/group order=data;
> define sex/across;
> define weight/display width=6;
> define height/display width=6;
> define dummy/sum noprint;  /* Dont want this printed */
> quit;  /* Changed from RUN  */
>
>
> I achieved results that I think you want.
>
> There is no need to specify preloadfmt or format on your NAME statement.
> I only use preloadfmt for ACROSS variables.
> I specified NOPRINT on the define statement, as I don't think you need
> this listed.
>
> You may need to create a format for GENDER.
>
> Hope this helps.
>
> Tom
>
> On Thu, 16 Aug 2007 10:37:24 -0400, Yu Zhang <zhangyu05@GMAIL.COM> wrote:
>
> >Hi,
> >
> >I am trying to use COMPLTEROWS option to show all possible subjects on my
> >report. the across variable is gender with two levels. the variable share
> >the same coumn with the GENDER is character type. it is defined
> >as "DISPLAY" in define statment. the problem i am having is I get too
> many
> >unwanted rows. Based on SAS DOC :
> >
> >"displays all possible combinations of the values of the group variables,
> >even if one or more of the combinations do not occur in the input data
> >set. Consequently, the row headings are the same for all logical pages of
> >the report within a single BY group."
> >
> >I shouldn't get that many rows(each subjects have 37 rows). Can someone
> >explain why I am having 37 rows for each subject.
> >
> >Thank you.
> >
> >
> >The code below will demonstrate my problem. when you run through the
> code,
> >please check the sas dataset OUT to see # of the OBS per subject.
> >
> >
> >
> >
> >data class;
> >retain fmtname 'nmlst' type 'c';
> >set sashelp.class (keep=name) end=last;
> >start=name;
> >label=name;
> >output;
> >if last then do;
> >name='Yu';
> >start=name;
> >label=name;
> >output;
> >end;
> >run;
> >
> >
> >proc format cntlin=class;
> >run;
> >
> >proc report data=sashelp.class nowd Completerows out=out;
> >column name sex,(weight height) dummy;
> >define name/group preloadfmt order=data format=$nmlst. ;
> >define sex/across;
> >define weight/display;
> >define height/display;
> >define dummy/computed;
> >run;
>