merge by two variables produce zero ouput observation

[mmhxc5]

Hi,

I would like to merge two datasets by STRUCTURE_NUMBER_008 and Inspection_Year if available in both datasets and keep all the variables in the output. when I run the code, it gives zero observation from the merge statement.

data SNOW_FIRNSA05;
merge all_data(in=one) snow_data(in=two) ;
by STRUCTURE_NUMBER_008 Inspection_Year;
if one;
run;

The format for the BY Variables is the same, but I do not know why the code does not work.

I appreciate the time and help I anyone could help me fix this error.

[mkeintz]

Please provide the log, so that we don't have to reproduce it.

[ballardw]

Formats don't matter much, it is the actual value used in the merge.

From the code you show one strongly suspects there is another issue. Rerun the code and copy from the log the code and any messages, paste on the forum into a code box opened with the </> icon to preserve formatting of the text.

[FreelanceReinh · Accepted solution]

Hi @mmhxc5,

It looks like Inspection_Year -- a char 15 variable (why not char 4 or, even better, numeric?) -- is left-aligned in ALL_DATA, but right-aligned in SNOW_DATA. So, the values from SNOW_DATA, e.g., '           2019' don't match those from ALL_DATA, e.g., '2019           ' because of the leading blanks. (Trailing blanks are no problem.)

You can create a new dataset (or overwrite SNOW_DATA) with a left-aligned variable Inspection_Year and use this dataset in the MERGE step:

data snow_data1;
set snow_data;
Inspection_Year=left(Inspection_Year);
run;

But I would recommend that you fix the issue (and possibly similar issues with other variables) at the stage where your datasets were initially created (from raw data or other sources), i.e., define variables with reasonable and consistent types, lengths and alignments.