topic Re: proc transpose in SAS Programming

proc transpose

monday89 — Sun, 15 Dec 2019 04:42:28 GMT

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;

Re: proc transpose

ed_sas_member — Sun, 15 Dec 2019 09:01:01 GMT

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;

Re: proc transpose

Ksharp — Sun, 15 Dec 2019 10:03:07 GMT

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;

Re: proc transpose

art297 — Sun, 15 Dec 2019 15:25:08 GMT

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

Re: proc transpose

ghosh — Sun, 15 Dec 2019 17:39:47 GMT

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;

*/