topic Re: merge by two variables produce zero ouput observation in SAS Programming

merge by two variables produce zero ouput observation

mmhxc5 — Thu, 06 Aug 2020 21:28:45 GMT

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.

Re: merge by two variables produce zero ouput observation

mkeintz — Thu, 06 Aug 2020 21:47:32 GMT

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

Re: merge by two variables produce zero ouput observation

ballardw — Thu, 06 Aug 2020 21:54:09 GMT

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.

Re: merge by two variables produce zero ouput observation

FreelanceReinh — Fri, 07 Aug 2020 18:16:37 GMT

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.