Have:
category | group | frequency | percent |
female | treatment | 3 | 33.33 |
male | treatment | 6 | 66.67 |
female | placebo | 1 | 33.33 |
male | placebo | 2 | 66.67 |
Want:
category | treatment_frequency | treatment_percent | placebo_frequency | placebo_percent |
female | 3 | 33.33 | 1 | 33.33 |
male | 6 | 66.67 | 2 | 66.67 |
Tried:
proc transpose data=c.demographics_freq out=c.test prefix=group_;
by category ;
id frequency percent;
var group;run;
Hi @monday89
It seems that you already posted this question:
https://communities.sas.com/t5/SAS-Programming/proc-transpose-many-columns/m-p/611836
There are many answer attempts. Please check them.
This one for example should work. NB: you can't do that in a single proc transpose.
data demographics_freq;
input category $1-6 group $8-16 frequency 18 percent 20-24;
cards;
female treatment 3 33.33
male treatment 6 66.67
female placebo 1 33.33
male placebo 2 66.67
;
run;
data c.test;
merge c.demographics_freq (where=(group="treatment") rename=(frequency = treatment_frequency percent = treatment_percent))
c.demographics_freq (where=(group="placebo") rename=(frequency = placebo_frequency percent = placebo_percent));
by category;
drop group;
run;
ED has already given you a great solution.
Another one is double transpose.
data demographics_freq;
input category $1-6 group $8-16 frequency 18 percent 20-24;
cards;
female treatment 3 33.33
male treatment 6 66.67
female placebo 1 33.33
male placebo 2 66.67
;
run;
proc sort data=demographics_freq;by category group;run;
proc transpose data=demographics_freq out=test;
by category group ;
var frequency percent;
run;
proc transpose data=test out=want delimiter=_;
by category;
id group _name_;
var col1;
run;
You already have two good solutions, but here is a third that would also work and can easily accommodate more variables:
data demographics_freq;
input category $1-6 group $8-16 frequency 18 percent 20-24;
cards;
female treatment 3 33.33
male treatment 6 66.67
female placebo 1 33.33
male placebo 2 66.67
;
run;
filename tr url 'https://raw.githubusercontent.com/art297/transpose/master/transpose.sas';
%include tr ;
%transpose(data=demographics_freq, out=want, by=category, sort=yes,
var=frequency percent, id=group, delimiter=_, var_first=no)
Art, CEO, AnalystFinder.com
Here is a solution building upon your line of thought. I used a macro to cycle through the two analysis variables, however, the non-macro code is in the comments below it
data have;
input category $1-6 group $8-16 frequency 18 percent 20-24;
cards;
female treatment 3 33.33
male treatment 6 66.67
female placebo 1 33.33
male placebo 2 66.67
;
run;
proc sort; by category; run;
%let vars=%str(frequency percent) ;
%macro trans;
%local i _var;
%do i=1 %to %sysfunc(countw(&vars));
%let var = %scan(&vars, &i);
proc transpose data=have out=&var (drop=_name_) suffix=_&var;
by category ;
id group;
var &var;
run;
%end;
data want;
merge &vars;
by category;
run;
proc print;
id category;
run;
%mend;
%trans;
/*
proc transpose data=have out=frequency (drop=_name_) suffix=_frequency;
by category ;
id group;
var frequency;
run;
proc transpose data=have out=percent(drop=_name_) suffix=_percent;
by category ;
id group;
var percent;
run;
data want;
merge frequency percent;
by category;
run;
*/
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!
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.