topic Re: Merge/Combine datasets - summing like variables. SAS 9.4 in SAS Programming

Merge/Combine datasets - summing like variables. SAS 9.4

antwon — Fri, 03 Feb 2017 02:10:29 GMT

Hello - thanks in advance for your time.

I have dataset1 with 100+ variables and dataset2 with 100+ variables. Some of the variables match across the two datasets. I'm looking for a dataset3 that is the sum of the matching variables but also includes the unique variables from each dataset. I understand this is a relatively simple task by renaming the variables, merging the datasets and summing the like variables manually. However I'm looking to remove any manual processes from this program as I receive this data often and these manual steps are driving me crazy! Additionally, the variable naming conventions do not follow any sequence, i.e., x1 - xn.

For example:

data have1;
input geo time 
	web_wib_aln
	web_wib_ccd
	web_wib_dch
	web_wib_dsv
	web_wib_efs
	web_wib_hel
	web_wib_iau;
datalines;
501 209 0 0 1 2 3 4 5
501 210 0 0 1 2 3 4 5
501 211 0 0 1 2 3 4 5
;
RUN;

data have2;
input geo time 
	web_wib_ccd
	web_wib_dch
	web_wib_dsv
	web_wib_ilh
	web_wib_inv
	web_wib_mtg
	web_wib_onl;
datalines;
501 209 6 7 8 9 10 11 12
501 210 6 7 8 9 10 11 12 
501 211 6 7 8 9 10 11 12
;
RUN;

The dataset I'm looking for:

data want;
input geo time
	web_wib_ccd
	web_wib_dch
	web_wib_dsv
	web_wib_aln
	web_wib_efs
	web_wib_hel
	web_wib_iau
	web_wib_ilh
	web_wib_inv
	web_wib_mtg
	web_wib_onl;
datalines;
501	209	11	8	10	1	3	4	5	9	10	11	12
501	210	9	8	10	1	3	4	5	9	10	11	12
501	211	10	8	10	1	3	4	5	9	10	11	12
;
run;

Again, thanks for your time and input!

Re: Merge/Combine datasets - summing like variables. SAS 9.4

anoopmohandas7 — Fri, 03 Feb 2017 03:00:39 GMT

Try an inner join and I have assumed the variable 'time' could be used as a key. This should give you the desired results.

proc sql; 
select a.geo, a.time, 
	a.web_wib_aln,
	a.web_wib_ccd,
	a.web_wib_dch,
	a.web_wib_dsv,
	a.web_wib_efs,
	a.web_wib_hel,
	a.web_wib_iau,
	b.web_wib_ilh,
	b.web_wib_inv,
	b.web_wib_mtg,
	b.web_wib_onl
from have1 as a
inner join have2 as b 
on a.time=b.time ;
quit ;

Re: Merge/Combine datasets - summing like variables. SAS 9.4

art297 — Fri, 03 Feb 2017 03:43:02 GMT

If we can assume that the math in your example doesn't add up correctly, then the following might do what you want:

data want;
  set have2 have1;
run;

proc summary data=want nway;
  var web_:;
  class geo time;
  output out=want (drop=_:) sum=;
run;

HTH,

Art, CEO, AnalystFinder.com

Re: Merge/Combine datasets - summing like variables. SAS 9.4

antwon — Fri, 03 Feb 2017 17:39:30 GMT

Thank you, anoopmohandas7! But I was looking to take the manual process of typing variable names out of the program for the reason that the datasets actually include 200+ variables. art297's example below worked well. Thanks for your time and input - its much appreciated!

Re: Merge/Combine datasets - summing like variables. SAS 9.4

antwon — Fri, 03 Feb 2017 17:46:45 GMT

art297 - this is brilliant! And yes, your assumption is absolutely correct - I must have copied/pasted the wrong table. I didn't even think to use proc summary and I didn't realize you could specify all variables beginning with "web_" in the command. This perfectly solves my dilemma - thank you!

Re: Merge/Combine datasets - summing like variables. SAS 9.4

Peter_C — Fri, 03 Feb 2017 17:53:58 GMT

Concerned about this merge.....
Obviously only numerics can be summed.
What about character vars. Are these the keys?
If so, no column names are needed in the prog.
Data v/ view= v ;
Set dataset1 dataset2 ;
Run;

proc summary noprint nway data = v ;
Class _character_
Var _ numeric_ ;
Output out= want( drop= _: ) sum= ;
Run;