topic Re: sume observations on the base of variable content in SAS Programming

sume observations on the base of variable content

Sir_Highbury — Fri, 24 Jun 2016 11:36:38 GMT

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

Re: sume observations on the base of variable content

Loko — Fri, 24 Jun 2016 11:54:39 GMT

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;

Re: sume observations on the base of variable content

Urban_Science — Fri, 24 Jun 2016 12:01:22 GMT

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;

Re: sume observations on the base of variable content

RW9 — Fri, 24 Jun 2016 12:06:49 GMT

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.