Summing up the variables in SAS

[danwarags]

Hello everyone,

I have a sample dataset 

data sales;
input year formulation$ drug$ country$ jan feb mar;
datalines;
2007 liters xyz1.2 US 120 111 134
2007 liters xyz1.8 US 129 122 124
2007 vials  xyz1.2 US 123 132 122
2007 liters xyz1.8 US 122 111 164
2008 vials  xyz1.2 US 133 156 178
2008 vials  xyz1.8 US 122 123 126
2008 vials  xyz1.2 US 124 125 167
2008 liters xyz1.8 US 134 125 156
2009 vials  xyz1.2 US 124 136 234
2009 liters xyz1.8 US 134 125 238
2009 vials  xyz1.2 US 123 134 135
2009 liters xyz1.8 US 126 345 138
;

My output should look like this:

year  xyz1.2  xyz1.8   totalsales

2007      742     772       1514

2008      883     786       1669

2009      886    1106      1992

How should I code the above sample dataset in order to get this output. Please guide me. 

Thank you

[Astounding]

Should your output be a SAS data set, or should it be a report?

[stat_sas]

Please try this one. While creating data set just added totalsales variable to use that in proc tabulate.

data sales;
input year formulation$ drug$ country$ jan feb mar;
totalsales=jan+feb+mar;
datalines;
2007 liters xyz1.2 US 120 111 134
2007 liters xyz1.8 US 129 122 124
2007 vials xyz1.2 US 123 132 122
2007 liters xyz1.8 US 122 111 164
2008 vials xyz1.2 US 133 156 178
2008 vials xyz1.8 US 122 123 126
2008 vials xyz1.2 US 124 125 167
2008 liters xyz1.8 US 134 125 156
2009 vials xyz1.2 US 124 136 234
2009 liters xyz1.8 US 134 125 238
2009 vials xyz1.2 US 123 134 135
2009 liters xyz1.8 US 126 345 138
;
proc tabulate data=sales;
class year formulation drug;
var totalsales;
table year, drug=''*totalsales=''*sum=''*f=8.0 totalsales*all=''*sum=''*f=8.0;
run;

[AhmedAl_Attar]

proc sql;
Select year
,sum(xyz1_2) as xyz1_2
,sum(xyz1_8) as xyz1_8
,sum(totalsales) as totalsales
FROM (select year
,sum(jan,feb,mar) as totalsales
,(CASE when drug='xyz1.2' then calculated totalsales
else 0 end) AS xyz1_2
,(CASE when drug='xyz1.8' then calculated totalsales
else 0 end) AS xyz1_8
from sales
group by year
)
GROUP BY year
ORDER BY year;
quit;

[Ksharp]

data sales;
input year formulation$ drug$ country$ jan feb mar;
datalines;
2007 liters xyz1.2 US 120 111 134
2007 liters xyz1.8 US 129 122 124
2007 vials  xyz1.2 US 123 132 122
2007 liters xyz1.8 US 122 111 164
2008 vials  xyz1.2 US 133 156 178
2008 vials  xyz1.8 US 122 123 126
2008 vials  xyz1.2 US 124 125 167
2008 liters xyz1.8 US 134 125 156
2009 vials  xyz1.2 US 124 136 234
2009 liters xyz1.8 US 134 125 238
2009 vials  xyz1.2 US 123 134 135
2009 liters xyz1.8 US 126 345 138
;
run;
proc sql;
create table temp as
 select *,sum(sum) as totalsales
  from (
   select year,drug,sum(sum(jan,feb,mar)) as sum
    from sales
     group by year,drug
  )
   group by year ;
quit;
proc transpose data=temp out=want(drop=_name_);
 by year totalsales;
 var sum;
 id drug;
 idlabel drug;
run;