Another transposition question - multiple columns under one?

[jainamistryx]

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 🙂

[Ksharp]

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;

[singhsahab]

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 !!

[Ksharp]

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; 

[singhsahab]

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

[Ksharp]

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;

[singhsahab]

Thank you so much Sir 🙂 

[Ksharp]

Hi. I notice maybe you should use 

 full join

instead of

 inner join

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

[Ksharp]

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;