Masked variable then match merge or partial match merging

kfbaker0206 — Mon, 20 Aug 2018 18:31:49 GMT

I have two datasets and there are a couple of things that I would like to do. 1.) Dataset1 has the variables: Code, Description, Type. Dataset2 has just the code but it is masked. I would like to first mask the code variable in dataset1 with an "X" as the 4th character replacement in the string. Then I would like to take Datasets 1 & 2 and match the masked with the unmasked to create the dataset below. Also, I wanted to know if there was a way to partially match merge the datasets to get the results below. The only code that I have is the merging. All it is doing is stacking the data not replacing the data as expected. 2.) I wanted to see if I could link the variables code to the description (i.e. 2Y5X1 will always be associated with Description: Apples and Type: A. What I have been doing is the long way...manually changing it in excel, sorting it by ascending, copying it back into dataset 1. I hope that my question is clear. Please see below.

DataSet1
Code	Description	Type
2Y521	Apples	A
2Y522	Oranges	O
2Y523	Bananas	B
2Y524	Pears	P
2Y525	Grapes	G
2Y526	Raspberries	R
2Y527	Kiwi	K
2Y528	Cantaloupe	C

DataSet2
Mask Dataset to Look like this with the X as replacement for the 4th character in a string

Code
2Y5X1
2Y5X2
2Y5X3
2Y5X4
2Y5X5
2Y5X6
2Y5X7
2Y5X8

Then Merge with DataSet1 to Match DataSet2
DataSet2	Description	Type
2Y5X1	Apples	A	<---Then always associate 2Y5X1 with Apples and A
2Y5X2	Oranges	O
2Y5X3	Bananas	B
2Y5X4	Pears	P
2Y5X5	Grapes	G
2Y5X6	Raspberries	R
2Y5X7	Kiwi	K
2Y5X8	Cantaloupe	C

 /*This is after I mask it in excel and import*/ 

proc sort data=dataset1; 
by code; 
run;
 
proc sort data=dataset2; 
by code; 
run;

Data Merge; 
merge dataset1 dataset2; 
by code; 
run;

Re: Masked variable then match merge or partial match merging

Jagadishkatam — Tue, 21 Aug 2018 05:15:26 GMT

data DataSet1;
input Code$ Description$ Type$;
code=prxchange('s/(\d\w\d)(\d)(\d)/$1X$3/',-1,code);
cards;	 	 	 	 	 
2Y521 Apples A   
2Y522 Oranges O   
2Y523 Bananas B	
2Y524 Pears P 	
2Y525 Grapes G 	 	 	 
;

data DataSet2;
input Code$;
code=prxchange('s/(\d\w\d)(\d)(\d)/$1X$3/',-1,code);
cards;	 	 	 	 	 
2Y521 
2Y522 
2Y523 
2Y524 
2Y525 
;

proc sort data=dataset1;
by code;
run;

proc sort data=dataset2;
by code;
run;

data want;
merge dataset1 dataset2;
by code;
run;

topic Masked variable then match merge or partial match merging in SAS Enterprise Guide

Masked variable then match merge or partial match merging

Re: Masked variable then match merge or partial match merging