topic Re: Table restructuring in SAS Programming

Table restructuring

PrinceAde — Sun, 02 Apr 2023 17:36:46 GMT

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%)

Re: Table restructuring

PaigeMiller — Sun, 02 Apr 2023 19:42:09 GMT

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.

Re: Table restructuring

Tom — Mon, 03 Apr 2023 01:57:29 GMT

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.

Re: Table restructuring

PrinceAde — Mon, 03 Apr 2023 07:15:31 GMT

Thank you for your response.

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;

data Ade_lib.demog;
set my_freq_rear
mymeans;
run;

Re: Table restructuring

Kurt_Bremser — Mon, 03 Apr 2023 07:59:38 GMT

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

Re: Table restructuring

PrinceAde — Mon, 03 Apr 2023 09:08:29 GMT

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 .

Re: Table restructuring

PrinceAde — Mon, 03 Apr 2023 09:12:48 GMT

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

Re: Table restructuring

A_Kh — Mon, 03 Apr 2023 14:22:32 GMT

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

Re: Table restructuring

PrinceAde — Wed, 05 Apr 2023 21:14:30 GMT

Thank you, sir. It is a class work, I will research the %tableN.
Thanks for your response.