A question of creating demographic table in the sas?

[Sikcion]

Hi guys! I'm creating a demographic table and having a question in the categorical variable.

So for the categorical variable AGegr1, I want to create a table like below:

I have utilized the proc freq function to create it, and it is easy to get the last 3 rows while i find it is hard to get the first row;

here is my code:

proc freq data=ADSL noprint;
tables Trt01an *AGegr1 / missing outpct out=AGegr1;
tables Trt01an/ missing out=AGegr1_n(drop=percent rename=(count=total));
run;

data AGegr1;
merge AGegr1(in=a) AGegr1_n(in=b);
by trt01an;
if a and b;
length value$11.;
value=put(count,3.)|| "(" || put(pct_row,4.1)||"%)";
run;

proc sort data=AGegr1;
by AGegr1;
run;

proc transpose data=AGegr1
out=AGegr1 (drop=_name_)
prefix=Trt;
by AGegr1;
var value;
id Trt01an;
run;

and my output:

there's still no first row, thanks for ur help!

[Kurt_Bremser]

Please supply an example of your initial dataset, in a DATA step with DATALINES. Do not skip this. If custom formats are used, do also include the PROC FORMAT code.

Use the "little running man" button to post the code.

[Patrick]

If you have some flexibility with the layout (which normally makes things much easier) then a simple Proc Tabulate could do the job.

proc format;
  value ageGroup
  low-12 = '<=12'
  12-14  = '>12 to 14'
  14-high= '>14'
  ;
run;

proc tabulate data=sashelp.class;
  class age sex;
  format age ageGroup.;
  table age all='Total', 
        (sex=' ' all='Total')*(n='Count' pctn='%'*f=5.1)
  ;
run;

If you need to stick to your layout then eventually Proc Report with some Break After logic could do it. Not sure - I don't have enough experience with this Proc to be sure so just an idea worth investigating.

[PaigeMiller]

@Sikcion I strongly endorse the table layout from @Patrick as something that is easy to produce, and in my opinion is just as readable as something that displays N and percent as a text string thusly: 3 (9.1%). Instead, the N and percent each have its own column, but everyone understand this and all the information is there, you lose nothing by adopting the table layout shown by @Patrick .

If it has to be in the format you showed in your original post, then please look at the %TABLEN macro, which will do this for you.

[tarheel13]

did you output 2 datasets from proc freq? it looks like those should be combined vertically, not horizontally.

[tarheel13]

You can try this code but use your data. 

proc format;
   value agefmt
   18 - 40= '18 - 40'
   41 - 64 = '41 - 64' 
   65 - HIGH = '>= 65'
   ;
   value trtf
   1='Active'
   2='Placebo'
   ;
run;

proc sql noprint;
   select count(distinct usubjid) into: n1
      from echo.dm
      where armcd='ECHOMAX';
   select count(distinct usubjid) into: n2
      from echo.dm
      where armcd='PLACEBO';
quit;
%put &=n1 &=n2;

proc means data = dm completetypes n noprint nway;
   format age agefmt. trt01an trtf.;
   class trt01an age / preloadfmt;
   var age;
   output out=agestats n=_n;
run;

proc sort data=agestats;
   by age;
run;

proc transpose data = agestats out=agestats_t prefix=col;
   by age;
   var _n;
run;

proc means data = dm completetypes n noprint nway; 
   format trt01an trtf.;
   class trt01an / preloadfmt;
   var trt01an;
   output out=trtcounts n=_n;
run;

proc sort data=trtcounts;
   by trt01an;
run;

proc transpose data = trtcounts out=trtcounts_t prefix=col;
   var _n;
run;

data stack;
   length cat0 $200;
   set trtcounts_t(in=a) agestats_t(in=b);
   if a then cat0='n';
   else cat0 = put(age,agefmt.);
   array trtcounts[2] (&n1 &n2);
   array cnts[2] col1 col2;
   array pcts[2] pct1 pct2;
   array values[2] $14 value1-value2;
   if b then do;
      do i=1 to dim(cnts);
         if missing(cnts[i])=0 then pcts[i] = 100*cnts[i]/trtcounts[i];
         values[i]=strip(put(cnts[i],8.))||'('||strip(put(pcts[i],5.1))||')';
      end;
   end;
   if a then do;
      do i= 1 to dim(cnts);
       values[i] = strip(put(cnts[i],8.));
      end;
   end;
   drop i;
run;

[Ksharp]

proc format;
  value ageGroup
  low-30 = '<=30'
  30<-60  = '>30 to 60'
  60<-high= '>60'
  ;
run;
data have;
 set sashelp.heart;
 _ageatstart=put(ageatstart,ageGroup. -l);
run;

proc sql;
create table want as
select 'n' as a length=80 label='Age group (yr)',bp_status,put(count(*),best. -l) as value length=80
 from have 
  where _ageatstart is not missing and bp_status is not missing
   group by bp_status
union all
select _ageatstart,bp_status,
cats(put(count(*),best. -l),'(',put(count(*)/(select count(*) from have where bp_status=a.bp_status),percent8.2),')')
 from have as a
  where _ageatstart is not missing and bp_status is not missing
   group by  _ageatstart,bp_status
;
quit;
proc report data=want nowd;
column a value,bp_status;
define a/group order=data;
define bp_status/across order=data descending;
define value/group '';
run;

[PaigeMiller]

I now present an opinion. Feel free to disagree.

When I talked about readability in my earlier post, it is my opinion that the table produced by @Patrick is the most readable; then the table at the beginning of the original message is next most readable, because there is a space between the N and the parenthesis enclosing the percent; and the least readable is the table above from @Ksharp , the lack of having a space between the N and the parenthesis making it harder to read than the others (although clearly this is easily fixed). If I was a professor grading the presentations that I have discussed, Patrick would get an A+, and the other table would get lower grades.

People don't think about readability because they are so highly focused on getting the code to work. But readability of your table is very important as well.

But also, in terms of coding, the table by Patrick takes the least effort. So I prefer that one for a number of reasons. 

[tarheel13]

yeah but you have the follow the table shell and this is very common. statisticians design the shells. 

[PaigeMiller]

---

@tarheel13 wrote:

yeah but you have the follow the table shell and this is very common. statisticians design the shells. 

---

I am a statistician, and I have never felt that I have to follow the layout shown. In fact, to me, making something readable is more important than following the exact layout shown.

[tarheel13]

but this is standard for pharmaceutical industry. programmers have to follow the shells. 

[Ksharp]

Paige,
It is about stuff submitted to FDA ,not about " If I was a professor grading the presentations".
I agreed with tarheel . There are standard table layout you have to follow up if you are in Pharam field . It is not up to you to discuss these questions, it is question for statisticians and FDA .
You just followed it if you are a sas programmer .