|
%macro makesqllist( DataIn = , PreFix = , ExcludeVars = ) ;
%Local DSID I Var Close List ;
%Let Dsid = %Sysfunc( Open ( &DataIn , IS ) ) ;
%Do I = 1 %To %Sysfunc( Attrn( &Dsid , Nvars ) ) ;
%Let Var = %Sysfunc( VarName( &Dsid , &I ) ) ;
%If ( %SysFunc( IndexW( %Upcase( &ExcludeVars ) , %Upcase( &Var ) ) ) = 0 )
%Then %Do ;
%Let List = &List &PreFix&Var ;
%End ;
%End ;
%Let Close = %Sysfunc( Close( &Dsid ) ) ;
%SysFunc( TranWrd( &List , %Str( ) , %Str( , ) ) )
%mend makesqllist ;
Proc SQL ;
Create Table Need As
Select A.* , %MakeSQLList( Data = B , Prefix = B. , ExcludeVars = Id Var1 )
From A As A
Left Join
B As B
On A.ID = B.ID
And A.Var1 = B.Var1 ;
Quit ;
Toby Dunn
If anything simply cannot go wrong, it will anyway. Murphys Law #2.
The buddy system is essential to your survival; it gives the enemy somebody
else to shoot at.
Murphys Law #
Tell a man there are 300 billion stars in the universe and he'll believe
you. Tell him a bench has wet paint on it and he'll have to touch to be
sure. Murphys Law #9
From: Will <schimpanski@GMX.DE>
Reply-To: Will <schimpanski@GMX.DE>
To: SAS-L@LISTSERV.UGA.EDU
Subject: PROC SQL: Drop double variables
Date: Thu, 12 Jul 2007 16:34:51 +0200
Hi,
I'd like to join/merge two large tables a and b where b contains a
subset of the variables in a (e.g. tid) and other variables.
Example:
Table a Table b
------------ --------------
id var1 var2 id var1 var3
1 3.4 10.2 1 3.4 20.1
2 2.9 11.3 2 2.9 21.3
3 3.1 13.7 3 3.1 23.5
4 4.2 10.3 4 4.2 21.1
Table ab
-----------------
id var1 var2 var3
1 3.4 10.2 20.1
2 2.9 11.3 21.3
3 3.1 13.7 23.5
4 4.2 10.3 21.1
How can I merge those tables with PROC SQL so the double
columns/variables in b (e.g. id, var1) are dropped _without_ referring
to them in the command in an explicit way? (N.B. As I have large
datasets with several hundred variable explicitly selecting the
variables would be quite a lot of work.)
At the moment the double variables are kept in the joined dataset which
causes problems when doing an "outer union corr" concatenation with
another third table.
Thanks in advance,
Will
_________________________________________________________________
Need a brain boost? Recharge with a stimulating game. Play now!
http://club.live.com/home.aspx?icid=club_hotmailtextlink1
| 
