topic Re: Summing up the variables in SAS in SAS Programming

Summing up the variables in SAS

danwarags — Mon, 28 Mar 2016 16:45:37 GMT

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

Re: Summing up the variables in SAS

Astounding — Mon, 28 Mar 2016 16:58:37 GMT

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

Re: Summing up the variables in SAS

stat_sas — Mon, 28 Mar 2016 17:14:58 GMT

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;

Re: Summing up the variables in SAS

AhmedAl_Attar — Mon, 28 Mar 2016 17:19:47 GMT

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;

Re: Summing up the variables in SAS

Ksharp — Tue, 29 Mar 2016 02:23:08 GMT


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;