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
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;
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;
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
PUTC/PUTN allow the second parameter, the format to be dynamic or variable driven.
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.
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;
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.
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.