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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: