Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

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

[ Edited ]

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


Accepted Solutions
Solution
‎10-13-2015 01:16 PM
Super User
Super User
Posts: 7,392

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

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 80

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

[ Edited ]

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

Community Manager
Posts: 2,757

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

[ Edited ]
I corrected the title. Perhaps an "autocomplete" from the Post Message form?
Solution
‎10-13-2015 01:16 PM
Super User
Super User
Posts: 7,392

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

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.

Frequent Contributor
Posts: 80

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

[ Edited ]
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

Frequent Contributor
Posts: 80

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

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

Respected Advisor
Posts: 3,777

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

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;

Capture.PNG

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 377 views
  • 0 likes
  • 4 in conversation