@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: