Is this still something you look for an answer? Or have you found a solution in-between? I've given your question some thought and I believe you shouldn't use "update metadata" at all. Proc Transpose is a very tricky procedure for metadata driven development and I believe SAS R&D has probabely not though it through when they've implemented this "update metadata" option. It might be that there is some very special use case covered requested by a customer which might have driven this - but I still can't see how one could reasonably use this option. For your case: It appears that you know which columns proc transpose could generate for you. So why don't you just define all possible columns in your target table. The Transform node generating the Proc Transpose statement will still create a physical table only containing the actual columns but then in the next node where you have the loader you can map all columns from source to target (as here you're using metadata information). The metadata table object and the underlying physical table won't be in sync so you willl most likely get some kind of error if selecting "view data" on the metadata table. I believe most (if not all) loader generated code will deal nicely with a physical table which has less columns than it's corresponding metadata table object. Just give it a try.
... View more
