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;
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;
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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.