topic THE LOOK UP VALUES in New SAS User

THE LOOK UP VALUES

hcbn — Wed, 20 Mar 2019 20:40:50 GMT

I would like to match the value in the following information to the input table. Please, see the desired output

Value	Value
KET	Cars
KOT	Jeans
PERSE	Dress
Van	Computer

INPUTS

KOT

VAN

KOT

VAN

KOT

PERSE

VAN

PERSE

KOT

VAN

PERSE

VAN

KOT

VAN

KET

VAN

KET

VAN

KOT

VAN

KOT

VAN

KET

PERSE

INPUTS	OUTPUT
KOT	Jeans
KOT	Jeans
VAN	Computer
KOT	Jeans
KOT	Jeans
KOT	Jeans
VAN	Computer
KOT	Jeans
KOT	Jeans
PERSE	Dress
VAN	Computer
VAN	Computer
PERSE	Dress
KOT	Jeans
KOT	Jeans
KOT	Jeans
KOT	Jeans
VAN	Computer
PERSE	Dress
VAN	Computer
KOT	Jeans
VAN	Computer
KET	Cars
VAN	Computer
VAN	Computer
KET	Cars
KET	Cars
VAN	Computer
KOT	Jeans
KOT	Jeans
VAN	Computer
VAN	Computer
KOT	Jeans
VAN	Computer
VAN	Computer
KET	Cars
PERSE	Dress
PERSE	Dress

Re: THE LOOK UP VALUES

ballardw — Wed, 20 Mar 2019 20:56:18 GMT

Looks like a case of a custom format. Use the information in the look up table to create a format.

Apply the format to the input variable as needed.

Re: THE LOOK UP VALUES

Reeza — Wed, 20 Mar 2019 22:08:55 GMT

I find this paper to be a good reference on Formats:
https://www.lexjansen.com/nesug/nesug04/pm/pm22.pdf

Re: THE LOOK UP VALUES

VDD — Wed, 20 Mar 2019 22:10:36 GMT

if you want just a SQL match this will give you what you want as long as Van and VAN are the same in your expectations.

data have;
input ID $ Value $;
datalines;
KET Cars
KOT Jeans
PERSE Dress
Van Computer
;
 
data inputs;
input ID $;
cards;
KOT
KOT
VAN
KOT
KOT
KOT
VAN
KOT
KOT
PERSE
VAN
VAN
PERSE
KOT
KOT
KOT
KOT
VAN
PERSE
VAN
KOT
VAN
KET
VAN
VAN
KET
KET
VAN
KOT
KOT
VAN
VAN
KOT
VAN
VAN
KET
PERSE
PERSE
;
proc sql;
	create table want as
	select a.*, b.value
	from inputs as a,
		 have as b
	where upcase(a.id) = upcase(b.id);
quit;

Re: THE LOOK UP VALUES

Ksharp — Thu, 21 Mar 2019 12:05:30 GMT

I guess you want keep the order of obs in table INPUTS.

data have;
input ID $ Value $;
datalines;
KET Cars
KOT Jeans
PERSE Dress
VAN Computer
;
 
data inputs;
input ID $;
cards;
KOT
KOT
VAN
KOT
KOT
KOT
VAN
KOT
KOT
PERSE
VAN
VAN
PERSE
KOT
KOT
KOT
KOT
VAN
PERSE
VAN
KOT
VAN
KET
VAN
VAN
KET
KET
VAN
KOT
KOT
VAN
VAN
KOT
VAN
VAN
KET
PERSE
PERSE
;
data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h(dataset:'have');
  h.definekey('id');
  h.definedata('value');
  h.definedone();
 end;
set inputs;
call missing(value);
rc=h.find();
drop rc;
run;