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 🙂
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;
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 !!
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;
Thank you Sir !! But my query is, does it's possible to accomplished by PROC SQL ?
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;
Thank you so much Sir 🙂
Hi. I notice maybe you should use
full join
instead of
inner join
due to some id only have 'total' or 'freq' .
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.