topic Re: Macro compare a list of char and numeric variables in SAS Programming

Macro compare a list of char and numeric variables

ICE1511 — Fri, 13 Dec 2019 19:54:34 GMT

Hello -I am attempting to compare a large number of numeric and character across multiple datasets. I have coded the numeric portion, but am looking for a way to include both character and numeric variables. In my code below, the &list includes only numeric variables. I imagine I could use a compare function for character, but I'm not sure on the best route to take. Here is my code:

OPTIONS MPRINT SYMBOLGEN ;
%MACRO DO_ALL(dsin1, dsin2, LIST );

%LOCAL i VAR ;
%DO i=1 %TO %SYSFUNC(COUNTW(&list,%str( )));
%LET VAR=%SCAN(&LIST,&i,%str( ));

PROC SQL;
CREATE TABLE COMP_&VAR.
AS SELECT DISTINCT
A.id,
A.&VAR AS &VAR._1,
B.&VAR AS &var._2,
(A.&VAR. - B.&VAR.) AS &VAR._DIFF

FROM &dsin1. A
INNER JOIN &dsin2. B
ON A.id=B.id;
QUIT;

PROC SQL;
CREATE TABLE CNT_&VAR
AS SELECT
"&VAR" AS VARIABLE,
COUNT(&VAR._DIFF) AS TOTAL
FROM COMP_&VAR.
where &VAR._DIFF NE 0 ;
QUIT;

%end;
%mend do_all;

/**1st datasets for compare **/
%do_all(base,compare,
numeric_var1 numeric_var2 numeric_var3 numeric_var4 numeric_var5 ) ;

/**2nd datasets for compare **/
%do_all(base2,compare2,
numeric_var1 numeric_var2 numeric_var3 numeric_var4 numeric_var5 ) ;

I would like to include something like this for the character variables and possibly use a compare(with maybe conditionals to subset the type??)

%do_all( base, compare, char1 char2 char3 char4 ) ;

Re: Macro compare a list of char and numeric variables

VDD — Fri, 13 Dec 2019 20:32:04 GMT

welcome to the world of comparing differences. Sometimes just using Proc compare works better for me that using a SQL statement for comparing. Other times I use the X commands to do compares when I need to do that for SAS datasets I create a temporary text version of the SAS data and call FC for comparing.

One suggestion would be to get the process to work without macros for comparing the character strings.

then re-macro it after the process works for a few different type of compares. -- Steady as she goes --

Re: Macro compare a list of char and numeric variables

Astounding — Sat, 14 Dec 2019 14:28:47 GMT

I would begin with:

case (when a.&var ne b.&var then cat(a.&var, '*' , b.&var else ' ') as &var._diff

There aren't a whole lot of rules when it comes to a character variable being different. So this will capture the differences.

An important question to consider: will there always be exactly 1 observation per ID in both data sets?