topic Re: Merging two datasets by variable in different formats in SAS Programming

Merging two datasets by variable in different formats

Novice_ — Tue, 05 Jul 2022 03:27:10 GMT

I have two datasets with an id variable to merge them with.

Data1:

variable name=id

variable type=character

length 255

format $255.

informat $255.

Data2:

variable name=patid

variable type= numeric

length 8

format 11.

informat 11.

I tried to use the following code to convert the numeric variable in data 2 to character for merging:

data new;
set data2;
id=put(patid, $255.);
run;

However, it gives me the following error:

ERROR 29-185: Width specified for format is invalid.

I tried a format with shorter length and then tried merging. The complete code is below:

data new;
set data2;
id=put(patid, $3.);
run;

proc sort data=data1;

by id;

run;

proc sort data=new;

by id;

run;

Data final;
merge data1(in=in1) new (in=in2);
by id;
if in1;
run;

The above code runs but for the variables within the data new, I am getting missing values in the final table. So the merge was actually not successful. Can anyone please help me what to do? Thanks.

Re: Merging two datasets by variable in different formats

Astounding — Tue, 05 Jul 2022 04:22:13 GMT

You will need to find out more about your data to make this happen.

While your variable is $255 in the first data set, how many characters are actually needed? Are all the values left-hand-justified?

You will need to produce an exact match between the two data sets, so you need to know what is in your data.

Re: Merging two datasets by variable in different formats

Kurt_Bremser — Tue, 05 Jul 2022 05:08:39 GMT

Both of the variable specifications point to an unstable data import process. How does your data arrive in SAS?

The fact that the numeric variant has a format of 11. lets me suspect that the id values are 11-digit strings, but you need to verify that with the character variant.

Re: Merging two datasets by variable in different formats

Novice_ — Tue, 05 Jul 2022 06:31:07 GMT

Thank you for the reply. These are SAS datasets in a SAS server

Re: Merging two datasets by variable in different formats

Novice_ — Tue, 05 Jul 2022 06:34:02 GMT

Thank you for replying. These are SAS datasets stored in a SAS server as such. How do I check more details on the said variable?

Re: Merging two datasets by variable in different formats

Kurt_Bremser — Tue, 05 Jul 2022 07:16:24 GMT

Then you must first get to really Know Your Data (Maxim 3). See how the IDs are stored in the character variant. Are they left- or right-aligned? Is there a constant number of digits, or does this change? Are there leading zeroes?

If you provide sufficient examples, we can help you further.

Although you work with SAS datasets, this does not mean the data originated in SAS (nobody uses SAS to run a business database). So I still hold it that the process that gets the data from the original source into SAS is flawed.

Re: Merging two datasets by variable in different formats

Novice_ — Mon, 18 Jul 2022 22:09:58 GMT

The variable patid prints as below:

Re: Merging two datasets by variable in different formats

Novice_ — Mon, 18 Jul 2022 22:12:40 GMT

Thank you for your reply. It seems it is left aligned. All ids have 3 characters. I so not see any leading zeros but I am not sure if there are blanks after. The variable prints as below:

Re: Merging two datasets by variable in different formats

Tom — Mon, 18 Jul 2022 22:48:52 GMT

@Novice_ wrote:

Thank you for your reply. It seems it is left aligned. All ids have 3 characters. I so not see any leading zeros but I am not sure if there are blanks after. The variable prints as below:

You cannot tell if the values are left aligned from looking at that photograph. The ODS output engine "eats" leading spaces.

Either print the value to plain old text output.

Or print the values using $QUOTE or $HEX format so you can see if there are leading spaces.

Re: Merging two datasets by variable in different formats

Bibishan_Rathod — Thu, 18 Jan 2024 11:45:32 GMT

Hi Team, I also having the same issue I want merging 5 table by using (in=). And renaming the variable and using the by statement by using the (=) is its works.

Re: Merging two datasets by variable in different formats

PaigeMiller — Thu, 18 Jan 2024 12:07:20 GMT

@Bibishan_Rathod wrote:

Hi Team, I also having the same issue I want merging 5 table by using (in=). And renaming the variable and using the by statement by using the (=) is its works.

Hello. I'm glad you are trying to help, but this is really way to brief to understand. Its really unclear what problem you are trying solve, and I don't understand the solution you describe. Please spend more time on this and explain in detail, with example data and with actual code.