DATA Step, Macro, Functions and more

sume observations on the base of variable content

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 127
Accepted Solution

sume observations on the base of variable content

Dear expert,

 

let's assume the following data set:

abc2016, bcd2015, a2015b, cd2016

10, 5, 2, 3

 

how can I sum filtering for the variables containing a certain value?

 

expected result:

- 2016: 13

- 2015: 7


Accepted Solutions
Solution
‎06-24-2016 07:59 AM
Super Contributor
Posts: 308

Re: sume observations on the base of variable content

Posted in reply to Sir_Highbury

Hello,

 

if you need it one time you can hard code it  as in my example otherwise you shall write a macro .

 

data have;
infile datalines dsd;
input abc2016 bcd2015 a2015b cd2016;
datalines;
10, 5, 2, 3
;
run;

proc sql noprint;
select name into :vr2015 separated by ' ' from
sashelp.vcolumn
where libname="WORK" and memname="HAVE" 
and name contains '2015';
select name into :vr2016 separated by ' ' from
sashelp.vcolumn
where libname="WORK" and memname="HAVE" 
and name contains '2016';
quit;

data want;
set have;
array sumv2015 {*} &vr2015;
array sumv2016 {*} &vr2016;
new_sum_2015=sum (of sumv2015{*});
new_sum_2016=sum (of sumv2016{*});
run;

View solution in original post


All Replies
Solution
‎06-24-2016 07:59 AM
Super Contributor
Posts: 308

Re: sume observations on the base of variable content

Posted in reply to Sir_Highbury

Hello,

 

if you need it one time you can hard code it  as in my example otherwise you shall write a macro .

 

data have;
infile datalines dsd;
input abc2016 bcd2015 a2015b cd2016;
datalines;
10, 5, 2, 3
;
run;

proc sql noprint;
select name into :vr2015 separated by ' ' from
sashelp.vcolumn
where libname="WORK" and memname="HAVE" 
and name contains '2015';
select name into :vr2016 separated by ' ' from
sashelp.vcolumn
where libname="WORK" and memname="HAVE" 
and name contains '2016';
quit;

data want;
set have;
array sumv2015 {*} &vr2015;
array sumv2016 {*} &vr2016;
new_sum_2015=sum (of sumv2015{*});
new_sum_2016=sum (of sumv2016{*});
run;
Occasional Contributor
Posts: 8

Re: sume observations on the base of variable content

A similar alturnative if your data is tall instead of wide.

data work.example;
	input variable1 $ value;
	datalines;
	abc2016 10
	bcd2015 5
	a2015b 2
	cd2016 3
	;
run;

proc sql;
	select case when variable1 contains "2015" then 2015
		when variable1 contains "2016" then 2016
		else 0 end as year
		, sum(value) as values
	from work.example
	group by calculated year
	;
quit;

 

Super User
Super User
Posts: 7,977

Re: sume observations on the base of variable content

[ Edited ]
Posted in reply to Sir_Highbury

Edit, as apposed to the above solutions, there is no hardcoding, and this will work for any number of numeric columns.

 

Another good example on how changing the structure of your data makes your programming life much easier.  Remember the data that comes out of coding doessn't have to be the strcuture that you use for your coding.  Make your life as simple as possible:

data have;
  abc2016=10;
  bcd2015=5;
  a2015b=2;
  cd2016=3;
run;
proc transpose data=have out=inter;
  var _numeric_;
run;
proc sql;
  create table WANT as
  select  distinct
          compress(_NAME_," ","a") as COL1,
          sum(COL1) as RESULT
  from    INTER
  group by compress(_NAME_," ","a");
quit;

This normalises your data, so that the year appears as "data" rather than as columns.  You only need to know there is an identifier and a result then, simple.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 205 views
  • 2 likes
  • 4 in conversation