How to create a List from PROC COMPARE to use in program

[SASHunter]

Hi,

I know that I have read something on how to do this, but after much searching yesterday - I am finally asking here. 

I am trying to create a listing of variables from a PROC COMPARE of two datasets.  And after capturing just the variables use them as a %LET statement to put in for a DROP= statment.

I can do the PROC COMPARE and get the list of variables.  But I had to cut and paste them into my program for the %let.  There are over 50 variables that show up.

options nodate pageno=1 linesize=80 pagesize=40;
proc compare base=sas14.aug14sup
compare=sas15.aug15sup out=work.temp
novalues brief listvar;
id var;
title 'Comparison of Variables in 2 Different Data Sets';
run;

Can someone give me a better way of programming this, without the cut and paste step. 

Thanks so much for any help here. 

Nancy

[SASHunter]

I don't know where that Title came from - That is not what I entered. ??? : \

[ChrisHemedinger]

I corrected the title. Perhaps an "autocomplete" from the Post Message form?

[RW9 · Accepted solution]

Not sure I follow you, but my take is you want to drop variables which only appear in one yes, if so:

proc sql noprint; 
  select  distinct NAME
  into    :DROP_LIST separated by " "
  from    (select NAME from DICTIONARY.COLUMNS where LIBNAME="SASHELP" and MEMNAME="CARS")
  where   NAME not in (select NAME from DICTIONARY.COLUMNS where LIBNAME="SASHELP" and MEMNAME="CLASS"); 
quit;
%put &DROP_LIST.;

This is just a general example.  Please provide test data in the form of datasteps, and required output if you require something more specific.

[SASHunter]

libname sas14 '.../prod/sas/aug14';
libname sas15 '.../prod/sas/aug15';

PROC SQL;
   SELECT distinct NAME
   into     :DROP_LIST separated by " "
   from   (select NAME from DICTIONARY.COLUMNS where upcase(libname)="SAS14" and upcase(MEMNAME)="AUG14SUP")
   where  NAME not in (select NAME from DICTIONARY.COLUMNS where upcase(libname)="SAS15"
                and upcase(MEMNAME)="AUG15SUP");
 QUIT;
 %PUT &DROP_LIST.;

I can not get the above to work for me. 

If I do the following I get a listing for the variables in 'aug14sup' just fine.

 [code]

options nodate pageno=1 source linesize=80 pagesize=60;

libname sas14 '/cpspb/prod/sas/aug';
%global drop_list;

proc sql;
   describe table dictionary.members;
   title 'SAS Files in SAS14 Library';
  select memname, memtype
    from dictionary.members
    where libname='SAS14';
 QUIT;

title " ";
PROC SQL;
   SELECT distinct NAME
     into    :DROP_LIST separated by " "
     from DICTIONARY.COLUMNS
     where upcase(LIBNAME)='SAS14' and upcase(MEMNAME)='AUG14SUP';
QUIT;
%put %DROP_LIST;   

[/code]

But if I try to combine the two libraries and the datasets using your example I get nothing. 

Actually I get - NOTE:  No rows were selected.

Thanks

[SASHunter]

I finally got this to work, by using the UPCASE in front of the LIBNAME statement as follows:

proc sql ;
   select distinct NAME
      into :dupvars separated by " "
   from (select NAME from DICTIONARY.COLUMNS where
         upcase(libname)="SAS15" and upcase(MEMNAME)='AUG15SUP')
   where NAME not in (select NAME from DICTIONARY.COLUMNS
       where upcase(libname)="SAS14" and
             upcase(MEMNAME)="AUG14SUP");
quit;
%put &dupvars;

Now - can I use that new &dupvars file in a program to drop these variables in a KEEP statement.

When I view the listing it has 'Column Name' above it, so I'm not sure if that would work.

Thanks,

Nancy

[data_null__]

I think this is what you want.

options center=0 ls=max;
filename FT74F001 temp;
proc printto new print=FT74F001;
   run;
proc compare base=sashelp.shoes(obs=5) compare=sashelp.shoes(firstobs=2 obs=6) brief novalues listvar;
   run;
proc printto;
   run;
data unequalvars;
   infile FT74F001;
   input @'NOTE: Values of the following' n @'variables compare unequal:' @;
   do i = 1 to n;
      input name:$32. @;
      output;
      end;
   stop;
   run;

proc print;
   run;