Create a function to exclude date and selected numeric variables using dictionary.colums

teja04 — Wed, 09 Jan 2019 16:56:17 GMT

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.

Re: Create a function to exclude date and selected numeric variables using dictionary.colums

ballardw — Wed, 09 Jan 2019 17:50:31 GMT

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

topic Re: Create a function to exclude date and selected numeric variables using dictionary.colums in SAS Programming

Create a function to exclude date and selected numeric variables using dictionary.colums

Re: Create a function to exclude date and selected numeric variables using dictionary.colums