Is it possible to unit test C macros for embedded software?

This is way too obscure. In general, I would strongly recommend not to hide super-trivial stuff like setting/clearing a pin behind abstraction layers.

Instead you should write something like:

#define LED_DDR  DDRD
#define LED_PORT PORTD
#define LED_PIN  (1u << 3)

LED_DDR  |= LED_PIN;  // set pin to output
LED_PORT |= LED_PIN;  // set pin to 1
LED_PORT &= ~LED_PIN; // set pin to 0
LED_PORT &= (uint8_t)~LED_PIN; // set pin to 0 with pedantic type safety (MISRA-C etc)
LED_PORT ^= LED_PIN;  // toggle pin

You can't really write clearer code so any abstraction layer beyond this is doomed to fail. It will not add clarity but it may add bugs. As seen from some thousands sketchy attempts on the SO/EE sites where people attempt to do just that. Or as seen in bloatware libs from silicon vendors.

A proper test would thus not test the obscure macro SET_INPUT_PIN but question its existence. The name of the test is code review. Which would also point out the following issues:

• There's absolutely no need to make this a variadic macro since it should accept exactly 2 arguments - not more, not less. This is nothing but sheer obfuscation.
• There is no type safety provided by this macro. The caller could literally pass anything to it and there's a big chance to silently pass compilation.
• Identifiers starting with an underscore, then followed by an upper case letter are reserved for libraries. So don't name macros _S etc, it might collide with the compiler libraries.
• The macro is naively written. The average coding standard out there will enforce something like ( (DDR ## port) &= ~(1 <<(pin)) ) with parenthesis both around macro parameters as well as around the final expression.
• The macro is using 1 signed integer constants which becomes a major hazard soon as we expand to 16 bit registers on what is likely a system with 16 bit int. We'll then get undefined behavior bugs all over the place. Should have been 1u.
• There's implicit promotion so the macro actually returns a 16 bit negative value int. Which is unhelpful and dangerous.

No, this is not possible to be done at compile time. You cannot use say, a #if inside of a macro substitution.

You can technically pass anything to the two arguments of _SET_INPUT_PIN. They are just blindly substituted by the pre-processor wherever they occur in the macro definition. You may pass constants to these, but they can also be variables. If it is a variable then it has no meaning at compile time since variables exist only at run-time. So there is no way of implementing this check at compile time. You can of course, implement a run-time check.

For example,

/*
Set a pin as an input
port  (B,C, D or E) 
pin  - pin to set (0-7)
*/
#define _SET_INPUT_PIN(port,pin) \do \
{ \
    if (pin < 8) \
        DDR ## port &= ~(1<<pin); \
    else \
        fail();\
}while(0)

Following what I sai in the comment above you can try using an assert() in your macro:

/*
    Set a pin as an input
    port  (B,C, D or E) 
    pin  - pin to set (0-7)
*/
#define _SET_INPUT_PIN(port,pin) do {                            \
                                       assert(pin>=0 && pin<8);  \
                                       DDR ## port &= ~(1<<pin); \
                                    }  while(0)
#define SET_INPUT_PIN(port, pin) _SET_INPUT_PIN(port, pin)

The assert checks the condition and produce an error. If compiling in C11, you can use the _Static_assert(condition, message) that checks the conditions just after macro expansion, and works as a macro control.

#define _SET_INPUT_PIN(port,pin) do {                            \
                                       _Static_assert(pin>=0 && pin<8, "Bad pin");  \
                                       DDR ## port &= ~(1<<pin); \
                                    }  while(0)

Please note also the use of parameters instead of the variable arguments that can lead to more errors.