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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.