Obsidian | Level 7

## Table restructuring

The 15(100%) are concatenated products of count and percent using proc freq, I eventually got dataset A using proc transpose, but my final destination should be B, with two columns Active and Placebo. The Gender is a row with an empty value for the columns.
Please how can I arrive at B using dataset A, any idea what I can do?
Thank you.

A
Gender active placebo
Male 15(100%) 13(100%)
Female 15(100%) 13(100%)

B
active placebo
Gender
Male 15(100%) 13(100%)
Female 15(100%) 13(100%)
8 REPLIES 8
Diamond | Level 26

## Re: Table restructuring

I would like to point out that rarely do I think it is worthwhile to combine numbers and percents into one text string — like 15(100%) — as you have done. Even if you have an absolute unyielding requirement to make the output look like table B, working hard to generate table A is the xy problem, it gets you somewhere, but not in a direction that gets you easily to your final destination.

One idea is to simply give up the fact that you want percents in parentheses in a text string, and do as I do, create a column in a table with the count and then the next column contains the percent. And if necessary you could simply try to talk people out of having number and percent in parentheses in a text string, and point out that this is the same information, but presented in an easier to read (and easier to create) format.

If it is an absolute requirement that the table look exactly like Table B, you should still skip table A, go back to the raw data and use the %TABLEN macro.

--
Paige Miller
Obsidian | Level 7

## Re: Table restructuring

Thank you, sir. It is a class work, I will research the %tableN.
Super User

## Re: Table restructuring

So A looks like it could be a dataset.  Perhaps something like this:

``````data a;
infile cards dsd dlm='|';
input Gender :\$7.  active :\$20. placebo :\$20.;
cards;
Male  |15(100%)|13(100%)
Female|15(100%)|13(100%)
;``````

But B does not look like a dataset.  What are the variable names?

Do you want to generate a REPORT that looks like that? If so why not generate the report from the dataset you used to generate A and just skip dataset A.

Obsidian | Level 7

## Re: Table restructuring

Any idea of what I can do from the begining to arrive at B?

This is my code below and how I arrived at table A;

data dmed;
set dmx1_dmy1;
keep gender active;
run;
proc freq data=dmed;
table gender active/ nopercent out=dm_freq;
run;

data cat_freq1;
set dm_freq;
length count 7 percent 6;
male= catx("(", put(count, 2.), put(percent, 3.), "%)");
female = catx("(",put(count, 2.), put(percent, 3.), "%)");
where active ne .;
drop count percent;
run;

data dfrem;
set dmx1_dmy1;
keep gender placebo;
run;
proc freq data=dfrem;
table gender placebo/ nopercent out=dm_freq2;
run;

data cat_freq2;
set dm_freq2;
length count 7 percent 6;
male= catx("(", put(count, 2.), put(percent, 3.), "%)");
female = catx("(",put(count, 2.), put(percent, 3.), "%)");
where placebo ne .;
drop count percent;
run;

data cat_freq1_freq2;
set cat_freq1 cat_freq2;
run;

proc sort data= cat_freq1_freq2 out=sorted_cat_freq;
by active;
run;

proc transpose data= sorted_cat_freq out=transpose_freq;
*by active;
var male female;
run;

data my_freq(rename=(_name_=Gender COL1=placebo col2=active));
set transpose_freq;
*drop active;
run;

proc sql;
create table my_freq_rear as
select gender, active, placebo
from my_freq;
quit;

set my_freq_rear
mymeans;
run;

Super User

## Re: Table restructuring

Please post an example for dataset dmx1_dmy1. Use a DATA step with DATALINES, so we can quickly recreate your dataset in our environment..

Obsidian | Level 7

## Re: Table restructuring

Thank you.

data dm;
input pat active \$gender age placebo;
datalines;
101   .    Male      37   0
102  1    Female  40    .
103  1    Male      50    .
104  .     Female  60   0
run;

Gender was initially 1 and 2, I formated it to male and female.

dmx1_dmy1 appear like this;

Pat     Active    Gender    Age      Placebo

101       .            Male       37           0

102       1          Female    40           .

103       1          Male        50           .

104       .           Female    60           .

Obsidian | Level 7

## Re: Table restructuring

Thank you.

data dm;
input pat active \$gender age placebo;
datalines;
101   .    Male      37   0
102  1    Female  40    .
103  1    Male      50    .
104  .     Female  60   0

;
run;

Gender was initially 1 and 2, I formated it to male and female.

dmx1_dmy1 appear like this;

Pat     Active    Gender    Age      Placebo

101       .            Male       37           0

102       1          Female    40           .

103       1          Male        50           .

104       .           Female    60           0

Lapis Lazuli | Level 10

## Re: Table restructuring

Hi,
Looks like you need a report, not a dataset. As @Tom suggested above, you could skip all data steps and use Proc Report.
Example;

``````data dm1;
set dm;
order=1;
run;

proc report data=dm1 out=have(drop= order _:);
column gender active placebo order;
define gender/'' display;
define active/display;
define placebo/display;
define order/order noprint;
compute before order;
line @1 text \$20.;
if order=1 then text= "Gender";
endcomp;
run; ``````

The output

Discussion stats
• 8 replies
• 842 views
• 1 like
• 5 in conversation