BookmarkSubscribeRSS Feed
Sikcion
Fluorite | Level 6

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:

Screenshot 2024-01-02 at 3.42.47 AM.png

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:

Screenshot 2024-01-02 at 3.51.02 AM.png

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

11 REPLIES 11
Kurt_Bremser
Super User

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
Opal | Level 21

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;

Patrick_0-1704196626339.png

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
Diamond | Level 26

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

--
Paige Miller
tarheel13
Rhodochrosite | Level 12

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

tarheel13
Rhodochrosite | Level 12

tarheel13_0-1704212591194.png

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
Super User

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;

Ksharp_0-1704250743324.png

 

PaigeMiller
Diamond | Level 26

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. 

--
Paige Miller
tarheel13
Rhodochrosite | Level 12

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

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
tarheel13
Rhodochrosite | Level 12

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

Ksharp
Super User
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 .

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1168 views
  • 2 likes
  • 6 in conversation