@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.
