Date:         Sat, 30 Apr 2005 19:20:53 -0400
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: Discovering Common Variables
Comments: To: sas-l@uga.edu

Harry Droogendyk wrote:
> Here's a solution using the open/attrn/varname functions.
>
> This is v8.2 code, could certainly be improved by upgrading to v9 (
> ie. cat* functions rather than trim()), and the array stuff could be
> replaced with a hash, more efficient than traversing the array
> multiple times.  Unfortunately, I don't have v9 available.  If more
> than two datasets are to examined, the code involving d2 ought to be
> a macro.
>
> data first;
>   a=1;b=2;c='abc';z='1234567890';
> run;
>
> data second;
>   b=7;c='def';y=2;
> run;
>
> data _null_;
>
>    array vars(1000)  $32;
>    length keep   $32000;
>
>    d1   = open('first');
>    num1 = attrn(d1,"nvars");
>    do i=1 to num1;
>       vars(i)=varname(d1,i);
>    end;
>    d1 = close(d1);
>
>    d2   = open('second');
>    num2 = attrn(d2,"nvars");
>    do i=1 to num2;
>         var=varname(d2,i);
>           do j = 1 to num1;
>               if var=vars(j) then do;
>                       keep = trim(keep)||' '||var;
>                         leave;
>                   end;
>           end;
>    end;
>    d2 = close(d2);
>
>    call symput('keep',trim(keep));
> run;
>
> data no_obs;
>    stop;
>    set first ( keep = &keep );
> run;
>
> proc contents data=no_obs;
> run;

Simple, as in short and elegant ? No.

Harry's solution is not robust as it does not check for differing types or
lengths when the names are the same.

The power of SCL shines in solving this problem.  SCL has a few functions
and variants up its sleeves that DATA Step does not.  Consider OPEN with
mode New, for creating new tables.  A cohort of this mode is the function
NEWVAR for defining the variables of the new table.
[Some other SCL only functions are COPY, DELETE, the IMG* family, and the
IC* family]

So, instead of using macro to communicate the columns of commoness in a
future step, the columns (in SCL) be directly created in a new table.

-------------------- SCL, compile and testaf (or run, or submit proc display
cat=)
init:
  dataset1 = 'sashelp.retail2';
  dataset2 = 'sashelp.prdsale';
  skeleton = 'work.common';

  ds1 = open (dataset1);
  ds2 = open (dataset2);
  dsnew = open (skeleton,'N');

  if not ds1 or not ds2 then return;

  do i = 1 to attrn(ds1, 'nvars');

    name   = varname (ds1,i);
    type   = vartype (ds1,i);
    length = varlen  (ds1,i);

    j  = varnum (ds2,name);
    if j = 0 then continue;

    type2 = vartype(ds2,j);
    if type ne type2 then continue;

    rc = newvar ( dsnew, name, type
                , max(length, varlen(ds2,j))  /* be graceful and honor the
longest of the two columns */
                , varlabel (ds1,i)  /* from dataset1 */
                , varfmt   (ds1,i)
                , varinfmt (ds1,i)
                );
  end;
return;

term:
  if ds1 then ds1 = close (ds1);
  if ds2 then ds2 = close (ds2);
  if dsnew then dsnew = close (dsnew);

  submit continue sql;
    dm 'var &skeleton';
  endsubmit;
return;
--------------------

Richard A. DeVenezia -- Learn how to customize SAS Explorer
http://www.devenezia.com/downloads/sas/actions/