BookmarkSubscribeRSS Feed
PrinceAde
Obsidian | Level 7
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
PaigeMiller
Diamond | Level 26

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
PrinceAde
Obsidian | Level 7
Thank you, sir. It is a class work, I will research the %tableN.
Thanks for your response.
Tom
Super User Tom
Super User

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.

PrinceAde
Obsidian | Level 7

 

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;
 

 

Kurt_Bremser
Super User

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

PrinceAde
Obsidian | Level 7

 

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           .

PrinceAde
Obsidian | Level 7

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

A_Kh
Lapis Lazuli | Level 10

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 
output.PNG

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 8 replies
  • 935 views
  • 1 like
  • 5 in conversation