SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Vlookup Table

Accepted Solution Solved
Reply
Contributor
Posts: 71
Accepted Solution

Vlookup Table

[ Edited ]

Hi,

Can you please help, my data looks like this:

 

Table 1

Field   val1 val2

Color   2      3

Color   1      1 

Test     1       1

Test      1      0

 

Table: Lookup table looks like this:

Field    Value    Name

Color     1            Red

Color     2            Orange

Color     3            Green

Test       1            yes

Test       0            no

 

Now I want Table 1 to merge with the Lookup table and add the new field "Name 1 and Name 2" -- my final table should look like this:

Field   val1 val2   Name1   Name2

Color   2      3      Orange   Green

 

Color   1      1      Red         Red

test     1       1        yes        yes

test      1       0      yes         no

 

Thanks


Accepted Solutions
Solution
‎03-24-2016 09:36 PM
Super User
Posts: 10,044

Re: Vlookup Table

[ Edited ]



data Table1;
input Field  $ val1 val2;
cards;
Color   2      3
Color   1      1 
Test     1       1
Test      1      0
;
run;
 
data Lookup;
input Field $   Value    Name $;
cards;
Color     1            Red
Color     2            Orange
Color     3            Green
Test       1            yes
Test       0            no
;
run;
data want;
 if _n_ eq 1 then do;
  if 0 then set Lookup;
  declare hash h(dataset:'Lookup');
  h.definekey('Field','Value');
  h.definedata('Name');
  h.definedone();
 end;
set Table1;
call missing(Name);
rc=h.find(key:Field ,key:val1 ); Name1=Name;
call missing(Name);
rc=h.find(key:Field ,key:val2 ); Name2=Name;
drop rc value Name ;
run;


View solution in original post


All Replies
Super User
Posts: 5,516

Re: Vlookup Table

[ Edited ]

Here's one approach:

 

proc format cntlin=lookup (rename=(field=fmtname value=start name=label));

run;

 

That gives you a format equivalent to having hard-coded along these lines:

 

proc format;

value color 1='Red' 2='Orange' 3='Green';

run;

 

Then use the format in a DATA step:

 

data want;

set have;

name1 = put(val1, color.);

name2 = put(val2, color.);

run;

 

 

Contributor
Posts: 71

Re: Vlookup Table

Posted in reply to Astounding

Thank you this worked - but I have many variables and each needs a different formatting , i updated my data in my question, any insights on how I can call different formats for different values.

 

Thanks

Super User
Posts: 19,868

Re: Vlookup Table

PUTC/PUTN allow the second parameter, the format to be dynamic or variable driven. 

Contributor
Posts: 71

Re: Vlookup Table

Thank you , can you please show me an example how can I call in different formats for the field values?

 

I will appreciate your help.

Solution
‎03-24-2016 09:36 PM
Super User
Posts: 10,044

Re: Vlookup Table

[ Edited ]



data Table1;
input Field  $ val1 val2;
cards;
Color   2      3
Color   1      1 
Test     1       1
Test      1      0
;
run;
 
data Lookup;
input Field $   Value    Name $;
cards;
Color     1            Red
Color     2            Orange
Color     3            Green
Test       1            yes
Test       0            no
;
run;
data want;
 if _n_ eq 1 then do;
  if 0 then set Lookup;
  declare hash h(dataset:'Lookup');
  h.definekey('Field','Value');
  h.definedata('Name');
  h.definedone();
 end;
set Table1;
call missing(Name);
rc=h.find(key:Field ,key:val1 ); Name1=Name;
call missing(Name);
rc=h.find(key:Field ,key:val2 ); Name2=Name;
drop rc value Name ;
run;


Contributor
Posts: 71

Re: Vlookup Table

That worked -- Thank you Thank you
Super User
Super User
Posts: 7,076

Re: Vlookup Table

Use your lookup table to define formats.  You can use the CNTLIN= option on PROC FORMAT to do it from the data if you want.

Then just use arrays to loop over your VAL variables and create the value of your NAME variables using PUTN() function.

 

data want ;
  set have ;
  array value val1-val2 ;
  array name $32 name1-name2 ;
  do over value ;
    name = putn(value,cats(field,'.'));
  end;
run;

If you don't like DO OVER then use DO I=1 to DIM(VALUE) and add all of the extra index notations to the array name references.

Super User
Super User
Posts: 7,076

Re: Vlookup Table

Use your lookup table to define formats.  You can use the CNTLIN= option on PROC FORMAT to do it from the data if you want.

Then just use arrays to loop over your VAL variables and create the value of your NAME variables using PUTN() function.

 

data want ;
  set have ;
  array value val1-val2 ;
  array name $32 name1-name2 ;
  do over value ;
    name = putn(value,cats(field,'.'));
  end;
run;

If you don't like DO OVER then use DO I=1 to DIM(VALUE) and add all of the extra index notations to the array name references.

 

Established User
Posts: 1

Re: Vlookup Table

PROC SQL solution, using @Ksharp's data sets above:

 

data Table1;
input Field  $ val1 val2;
cards;
Color   2      3
Color   1      1 
Test     1       1
Test      1      0
;
run;
 
data Lookup;
input Field $   Value    Name $;
cards;
Color     1            Red
Color     2            Orange
Color     3            Green
Test       1            yes
Test       0            no
;
run;

proc sql;
	create table want 
	as
	select T.Field
		, T.val1
		, T.val2
		, L1.Name as Name1
		, L2.Name as Name2
	from Table1 as T
	left join Lookup as L1
	 on L1.Value = T.Val1
	 and L1.Field = T.Field
	left join Lookup as L2
	 on L2.Value = T.Val2
	 and L2.Field = T.Field;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 814 views
  • 4 likes
  • 6 in conversation