topic Re: Vlookup Table in SAS Data Management

Vlookup Table

AZIQ1 — Thu, 24 Mar 2016 23:32:39 GMT

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

Re: Vlookup Table

Astounding — Thu, 24 Mar 2016 18:33:52 GMT

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;

Re: Vlookup Table

AZIQ1 — Thu, 24 Mar 2016 23:34:00 GMT

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

Re: Vlookup Table

Ksharp — Tue, 29 Mar 2016 13:54:57 GMT




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;

Re: Vlookup Table

AZIQ1 — Fri, 25 Mar 2016 01:37:04 GMT

That worked -- Thank you Thank you

Re: Vlookup Table

Reeza — Fri, 25 Mar 2016 02:58:03 GMT

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

Re: Vlookup Table

AZIQ1 — Sat, 26 Mar 2016 20:10:18 GMT

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.

Re: Vlookup Table

Tom — Sat, 26 Mar 2016 22:45:34 GMT

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.

Re: Vlookup Table

Tom — Sat, 26 Mar 2016 22:46:50 GMT

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.

Re: Vlookup Table

DSnyder — Thu, 31 Mar 2016 23:35:15 GMT

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;