BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sir_Highbury
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

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

3 REPLIES 3
Loko
Barite | Level 11

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;
Urban_Science
Quartz | Level 8

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;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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