Hello team,
I am going to join two datasets through a common field. But the variable in one dataset has this format: 1000 and it has this format in second dataset: 1000*01.
How can take *01 from the second dataset to make variables look same.
Regards,
BlueBlue
You could convert the values in one of the dataset into the style that the other has.
Say your two datasets are named STAR and NO_STAR and the variable is question is named ID.
data new_star;
set star;
old_id=id;
id = scan(id,1,'*');
run;
data want;
merge no_star new_star;
by id;
run;
Or with SQL syntax you could to the conversion and join in one step.
proc sql ;
create table want as
select *
from star(rename=(id=old_id)) a
full join no_star b
on scan(a.old_id,1,'*') = b.id
;
quit;
Note that if you have repeats of ID values in both datasets then the results will be different using SQL to join the records than what happens with a data step merge.
Please remember when talking about SAS data sets the Format has a very specific meaning as in a property assigned to variables that control how the values display. Appearance can have very little in common with actual values.
For example I have multiple variables that have values like 1 and 2 but the appearance because of the assigned format is 'Male' or 'Female'.
So, are you talking about Format or Value when you say " variable in one dataset has this format: 1000 and it has this format in second dataset: 1000*01."? Note that when you mention a value of 1000 you really need to state if the value is numeric 1000 or character "1000" as this has a major impact on how you compare values reliably.
You probably should include more than one example value as we don't know if 1) every single value ends in "*01", is more than 7 characters long and 3) whether the conversion needs to become numeric or not. Also, the presence of leading spaces can make a significant difference.
Post example data in usable and unambiguous form. Use a data step with datalines so we can be sure about data types, raw content, and formats.
DO NOT SKIP THIS.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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: