topic Re: Join two sets of data with a field with a character data type in SAS Programming

Join two sets of data with a field with a character data type

GN0001 — Wed, 19 Jan 2022 20:33:26 GMT

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

Re: Join two sets of data with a field with a character data type

Tom — Wed, 19 Jan 2022 20:50:53 GMT

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.

Re: Join two sets of data with a field with a character data type

ballardw — Wed, 19 Jan 2022 21:01:52 GMT

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.

Re: Join two sets of data with a field with a character data type

Kurt_Bremser — Thu, 20 Jan 2022 09:45:37 GMT

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.