I have two datasets I want to merge, keeping only the rows found in dataset 2; they have a common variable, weight in grams, but those variables aren't named or formatted (num/char) the same.
Dataset 1 has: Common CHAR $3
Dataset 2 has: CV NUM 4.
Something else I realized when looking at the variables sorted side by side, is that DS1 has an irregular number of leading zeros, depending on which observation it is, so I need a way to remove them so they can match.
Dataset | DS1 |
DS2 -- > what I want to match to |
|
||
Lowest weight (grams) | 0010 | 10 |
Highest weight (grams) | 0865 | 865 |
You cannot match numbers to characters. It will be easier to convert the strings into numbers since there is no difference between 0010 and 10 as numbers.
proc sql;
create table want as
select *
from one inner join two
on input(one.common,32.) = two.cv
;
quit;
You cannot match numbers to characters. It will be easier to convert the strings into numbers since there is no difference between 0010 and 10 as numbers.
proc sql;
create table want as
select *
from one inner join two
on input(one.common,32.) = two.cv
;
quit;
Thank you! That was a solution that was right under my nose, I just didn't see it..
You cannot have 4 digits in your dataset 1, as the variable is defined with a length of 3. Please review your question, and make sure that you get all types and attributes correct.
Because the strings posted included periods I assumed the information posted was the FORMATs attached to the variables and not the LENGTH of the variables. There is no need to include a period when specifying the LENGTH of a variable since a length can only be an integer. The format attached to a character variable does not modify the LENGTH of the values stored in the variable.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.