@teja04 wrote: Hi all, I have two dates with same variables. Before merging these two datasets for calculation. I want to exclude selected numeric variables and all data variables. Below is my code: data a; input c1 $ c2 $ c3 $ n1 n2 n3; datalines; ch1 ch2 funded 1 2 3 ch4 ch5 funded 4 5 6 ; run; data b; input c1 $ c2 $ c3 $ n1 n2 n3; datalines; ch1 ch2 total 7 8 9 ch4 ch5 total 10 11 12 ; run; data c; input c1 $ c2 $ c3 $ val; datalines; ch1 ch2 funded 1 ch4 ch5 funded 4 ; run; data d; input c1 $ c2 $ c3 $ val; datalines; ch1 ch2 total 7 ch4 ch5 total 10 ; run; %macro caldiff(ds1=, ds2=, fundvar=); options mprint mlogic symbolgen; proc sql; select distinct name into: charvars separated by ' ' from dictionary.columns where memname="%upcase(&ds1)" and libname='WORK' and upcase(type)='CHAR' and upcase(name) ne "%upcase(&fundvar)"; select distinct name into: numvars separated by ' ' from dictionary.columns where memname="%upcase(&ds1)" and libname='WORK' and upcase(type)='NUM' and upcase(name) ne "%upcase(&fundvar)"; select distinct strip(name)||"_N" into: newnumvars separated by ' ' from dictionary.columns where memname="%upcase(&ds1)" and libname='WORK' and upcase(type)='NUM' and upcase(name) ne "%upcase(&fundvar)"; quit; data &ds2(drop=&numvars i); set &ds2; array old {*} &numvars; array new {*} &newnumvars; do i=1 to dim(old); new{i} = old{i}; end; run; data &ds1._new(drop=&newnumvars j); merge &ds1(in=fund) &ds2(in=tot); by &charvars; array old {*} &numvars; array new {*} &newnumvars; if fund and tot then do; do j=1 to dim(old); old{j} = new{j} - old{j}; end; output; end; run; %mend caldiff; %caldiff(ds1=c, ds2=d, fundvar=c3); %caldiff(ds1=a, ds2=b, fundvar=c3); For Example, I want to exclude N1 variable from calculation. Could someone please help me in issue.
Please describe the rule for how to know that N1 is a variable to exclude.
Or are you looking for a way to provide a list of values instead to use? If that is the case then perhaps you are looking for something like this:
%macro caldiff(ds1=, ds2=, fundvar=);
options mprint mlogic symbolgen;
%let ds1 =%upcase(&ds1.);
%let ds2 =%upcase(&ds2.);
%let temp =%upcase(&fundvar.);
%let fundvar=;
/* this do loop creates a quoted list*/
%do i=1 %to %sysfunc(countw(&temp));
%let fundvar = &fundvar. %sysfunc(quote(%scan(&temp.,&i.)));
%end;
proc sql;
select distinct name into: charvars separated by ' '
from dictionary.columns
where memname="&ds1." and
libname='WORK' and
upcase(type)='CHAR' and
upcase(name) not in ( &fundvar.);
select distinct name into: numvars separated by ' '
from dictionary.columns
where memname="&ds1." and
libname='WORK' and
upcase(type)='NUM' and
upcase(name) not in (&fundvar.);
select distinct strip(name)||"_N" into: newnumvars separated by ' '
from dictionary.columns
where memname="&ds1." and
libname='WORK' and
upcase(type)='NUM' and
upcase(name) not in ( &fundvar.);
quit;
/*other code*/
%mend;
I might suggest that your code might look a little cleaner by %upcase the macro variables before the first use so the remaining is a bit easier to read.
Also use of this construct:
data &ds2(drop=&numvars i);
set &ds2;
is extremely dangerous, especially while testing code as when the input and output set are the same as the original data set is completely replaced and determining where a code logic problem occurred may be extremely difficult.
Please note the use of the forum {I} icon to open a code box for pasting code. The main message windows will reformat text removing indents and such with resulting code hard to read.
... View more
