topic Re: Another transposition question - multiple columns under one? in New SAS User

Another transposition question - multiple columns under one?

jainamistryx — Tue, 16 Oct 2018 12:18:33 GMT

Hi !!

So this is the data set I currently have:

continent

industry variable A B C

0509 total 37 78 12

0509 freq 64 34 2

1315 total 85 26 73

1315 freq 37 95 82

But would prefer if it looked like this:

continent

A B C

industry total freq total freq total freq

0509 37 64 78 34 12 2

1315 85 37 26 95 73 82

How would I go about doing this so the variables are underneath the continents?

Thanks so much, I am a new user of SAS 🙂

Re: Another transposition question - multiple columns under one?

Ksharp — Tue, 16 Oct 2018 12:41:19 GMT

Do you want a dataset or a report ?

data have;
input industry   $  variable   $      A         B          C;
cards;
0509          total                37        78       12
0509          freq                 64        34        2
1315          total                85        26       73
1315          freq                 37       95       82
;
proc sort data=have ;
by industry    variable;
run;
proc transpose data=have out=temp;
by industry    variable;
var a b c;
run; 
proc report data=temp nowd;
column industry _NAME_,variable,col1;
define industry/group;
define _name_/across ' ';
define variable/across ' ';
define col1/analysis ' ';
run;

Re: Another transposition question - multiple columns under one?

singhsahab — Tue, 16 Oct 2018 15:07:59 GMT

Hello Sir,

Does it possible to create data set as well by using PROC SQL for same scenario ?

I'm looking forward for your suggestion !!

Re: Another transposition question - multiple columns under one?

Ksharp — Wed, 17 Oct 2018 12:27:57 GMT

So you want a TABLE ?

data have;
input industry   $  variable   $      A         B          C;
cards;
0509          total                37        78       12
0509          freq                 64        34        2
1315          total                85        26       73
1315          freq                 37       95       82
;
proc sort data=have ;
by industry    variable;
run;
proc transpose data=have out=temp;
by industry    variable;
var a b c;
run; 
proc sort data=temp ;
by industry  _NAME_;
run;
proc transpose data=temp out=want delimiter=_;
by industry ;
id _name_ variable;
var col1;
run;

Re: Another transposition question - multiple columns under one?

singhsahab — Wed, 17 Oct 2018 15:09:31 GMT

Thank you Sir !! But my query is, does it's possible to accomplished by PROC SQL ?

Re: Another transposition question - multiple columns under one?

Ksharp — Thu, 18 Oct 2018 12:35:14 GMT

data have;
input industry   $  variable   $      A         B          C;
cards;
0509          total                37        78       12
0509          freq                 64        34        2
1315          total                85        26       73
1315          freq                 37       95       82
;
proc sql;
create table want as
select a.industry,total_A,total_B,total_C,
freq_A,freq_B,freq_C
from
(
 select industry,A as total_A,B as total_B,C as total_C
  from have 
   where variable='total'
 ) as a
 inner join
 (
 select industry,A as freq_A,B as freq_B,C as freq_C
  from have 
   where variable='freq'
 ) as b
 on a.industry=b.industry;
quit;

Re: Another transposition question - multiple columns under one?

singhsahab — Fri, 19 Oct 2018 13:53:08 GMT

Thank you so much Sir 🙂

Re: Another transposition question - multiple columns under one?

Ksharp — Fri, 19 Oct 2018 14:10:18 GMT

Hi. I notice maybe you should use

 full join

instead of

 inner join

due to some id only have 'total' or 'freq' .

Re: Another transposition question - multiple columns under one?

Ksharp — Fri, 19 Oct 2018 14:14:04 GMT

Hi. maybe you need this.

data have;
input industry   $  variable   $      A         B          C;
cards;
0509          total                37        78       12
1315          freq                 37       95       82
;
proc sql;
select coalescec(a.industry,b.industry) as industry,total_A,total_B,total_C,
freq_A,freq_B,freq_C
from
(
 select industry,A as total_A,B as total_B,C as total_C
  from have 
   where variable='total'
 ) as a
 full join
 (
 select industry,A as freq_A,B as freq_B,C as freq_C
  from have 
   where variable='freq'
 ) as b
 on a.industry=b.industry;
quit;