LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2005, week 5)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Sat, 30 Apr 2005 12:32:01 -0400
Reply-To:     harry.droogendyk@RBC.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Harry Droogendyk <harry.droogendyk@RBC.COM>
Subject:      Re: Discovering Common Variables
Content-Type: text/plain; charset="iso-8859-1"

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;

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Howard Schreier <hs AT dc-sug DOT org> Sent: Saturday, April 30, 2005 11:50 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Discovering Common Variables

I'd like to create a data set which has no observations but which includes all variables common to two (or more) existing data sets and which inherits variable attributes.

This would be used as the base for subsequent append operations, using PROC APPEND or PROC DATASETS.

I can think of three ways to do this:

1. using a UNION CORRESPONDING operation in SQL

2. examining metadata from SASHELP.VCOLUMN or PROC CONTENTS and building a KEEP list in a macro variable

3. opening each data set in a separate DATA step and traversing an array to discover variable names, then proceeding as in #2

See code below for #1 and #2. I'm actually curious about whether there is a simple non-SQL way of doing this.

Any suggestions?

* First method;

proc sql; create table common1 as select * from sashelp.prdsale(obs=0) union corresponding select * from sashelp.retail (obs=0); quit;

* Second method;

data in_either; set sashelp.vcolumn; where libname='SASHELP' and memname in ('RETAIL','PRDSALE'); keep memname name; run;

proc sort data=in_either; by name; run;

data _null_; length common $ 10000; retain common; merge in_either(where=(memname='RETAIL' ) in=inr) in_either(where=(memname='PRDSALE') in=inp); by name; if inr and inp then common = catx(' ',common,name); call symput('common',common); run;

data common2; set sashelp.prdsale(obs=0) sashelp.retail (obs=0); keep &common; run; __________________________________________________________________________________________________________________________________

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.

Back to: Top of message | Previous page | Main SAS-L page