CODEWITHSUNDEEP
sas
Kavitha
Kavitha

Reputation: 371

Table Lookup in sas

I have 2 tables and I want to output by matching with MODEL and AGE of T1 with MODEL and AGE_BT of T2. I have used the code and am getting blank ouput. output should look like output table below. .

Table:1

MODEL    AGE1   AGE2    AGE3    AGE4  
SLOT1    0-10   10-20   20-35   35p  
SLOT1    0-10   10-20   20-35   35p  
SLOT1    0-10   10-20   20-35   35p 
SLOT2    0-10   10-20   20-35   35p
SLOT2    0-10   10-20   20-35   35p
SLOT3    0-10   10-20   20-35   35p

Table:2

   MODEL    AGE_BT          FHA
   SLOT1    0-10            600 
   SLOT1    10-20           435   
   SLOT1    20-35           210  
   SLOT1       35P           10  
   SLOT2    0-10            391  
   SLOT2    10-20           385   
   SLOT2    20-35           246
   SLOT2       35P            0  
   SLOT3    0-10            391  
   SLOT3    10-20           385   
   SLOT3    20-35           246
   SLOT3       35P            0 


OUTPUT TABLE

MODEL   AGE1    AGE2    AGE3    AGE4  SEG_AGE1  SEG_AGE2 SEG_AGE3   SEG_AGE4    
SLOT1   0-10    10-20   20-35   35p     600     435        210     10 
SLOT1   0-10    10-20   20-35   35p     600     435        210     10 
SLOT1   0-10    10-20   20-35   35p     600     435        210     10
SLOT1   0-10    10-20   20-35   35p     600     435        210     10 
SLOT1   0-10    10-20   20-35   35p     600     435        210     10 
SLOT1   0-10    10-20   20-35   35p     600     435        210     10  
SLOT2   0-10    10-20   20-35   35p     391     385        246      0 
SLOT2   0-10    10-20   20-35   35p     391     385        246      0  
SLOT2   0-10    10-20   20-35   35p     391     385        246      0
SLOT2   0-10    10-20   20-35   35p     391     385        246      0


%macro age(ageap);
%let n=%sysfunc(countw(&ageap));

proc sql;
create table segment_test2_  as
select distinct T1.*
%do i=1 %to &n;
%let ap = %scan(&ageap,&i);
,T2.FHA as SEG_&ap
%end;
from      segment_test1 T1
left join ANNUAL_FH     T2
on  T1.Model=T2.Model and T1.&ap=T2.AGE_BT;
quit;
%mend;

%age(AGE1 AGE2 AGE3 AGE4);

Upvotes: 0

Views: 121

Answers (2)

Kavitha
Kavitha

Reputation: 371

OPTIONS SYMBOLGEN  MPRINT MLOGIC;
%macro AG;

%LET AA=1;

%do i=0 %to 3;

%let apS = %EVAL(&AA+&i);

proc sql;
create table SEGMENT_&apS as
select distinct T1.*


,T2.Average_FH/12 as  Segment_&aps

from      segment T1
left join FINAL.ANNUAL_FH_AVERAGE T2
on  T1.Model=T2.Model and T1.age&aps=T2.Age_bracket ;
quit;
%end;

%mend AG;
%AG

Upvotes: 0

vasja
vasja

Reputation: 4792

With MPRINT, you should see this code:

74   options mprint;
75   %age(AGE1 AGE2 AGE3 AGE4);
MPRINT(AGE):   proc sql;
MPRINT(AGE):   create table segment_test2_ as select distinct T1.* ,T2.FHA as SEG_AGE1 ,T2.FHA as SEG_AGE2 ,T2.FHA as SEG_AGE3
,T2.FHA as SEG_AGE4 from segment_test1 T1 
left join ANNUAL_FH T2 on T1.Model=T2.Model and T1.AGE4=T2.AGE_BT;

Notice T1.AGE4=T2.AGE_BT at the end. Based on your sample, AGE_BT has to be left aligned and lower-cased to potentially equal AGE4 like this:

T1.AGE4=lowcase(left(T2.AGE_BT))

Upvotes: 3

Related Questions