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

Hi

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

 

ValueValue
KETCars
KOTJeans
PERSEDress
VanComputer

 

 

INPUTS
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

 

INPUTSOUTPUT
KOTJeans
KOTJeans
VANComputer
KOTJeans
KOTJeans
KOTJeans
VANComputer
KOTJeans
KOTJeans
PERSEDress
VANComputer
VANComputer
PERSEDress
KOTJeans
KOTJeans
KOTJeans
KOTJeans
VANComputer
PERSEDress
VANComputer
KOTJeans
VANComputer
KETCars
VANComputer
VANComputer
KETCars
KETCars
VANComputer
KOTJeans
KOTJeans
VANComputer
VANComputer
KOTJeans
VANComputer
VANComputer
KETCars
PERSEDress
PERSEDress

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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;

View solution in original post

4 REPLIES 4
ballardw
Super User

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.

Reeza
Super User
I find this paper to be a good reference on Formats:
https://www.lexjansen.com/nesug/nesug04/pm/pm22.pdf
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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;
Ksharp
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 542 views
  • 0 likes
  • 5 in conversation