Of course. However, when a SASer of Howard's stature posts a question, one gives the benefit of the doubt. Having said that, I realize not all readers have Howard's acumen. At the time of posting, I thought about adding the "type/length" disclaimer but didn't. In retrospect, I should have. Mea culpa.

Nice touch using varnum() to check the presence of the variable in the 2nd dataset, even better than a hash. ;-)

A question of interest for my own edification - Howard indicated that searching for common variables might involve more than 2 data sets. Without inciting another macro/SCL discussion, how would you extend the SCL solution to n data sets?

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Richard A. DeVenezia Sent: Saturday, April 30, 2005 7:21 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Discovering Common Variables

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/ __________________________________________________________________________________________________________________________________

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Ce courrier électronique est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courrier électronique par erreur, veuillez m'en aviser immédiatement, par retour de courrier électronique ou par un autre moyen.