BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AZIQ1
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User



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

9 REPLIES 9
Astounding
PROC Star

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;

 

 

AZIQ1
Quartz | Level 8

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

Reeza
Super User

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

AZIQ1
Quartz | Level 8

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.

Ksharp
Super User



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;


AZIQ1
Quartz | Level 8
That worked -- Thank you Thank you
Tom
Super User Tom
Super User

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.

Tom
Super User Tom
Super User

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.

 

DSnyder
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

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.

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