Hi, So I'm doing this in DI Studio although as User Written Code. Hopefully this makes sense, there's an example at the end to hopefully help.
The situation is each supplier needs different fields (from a selection of ~50) but all suppliers get the same output delimited file, just the fields they don't need are blank. I had it working when each supplier just needed their fields but we now need everyone to get the same template, with just their fields populated. For example one supplier may need everyones Name, Address, DoB, another might just need Names and DoBs etc. It'll be a different combination for each and even the order may change (field names will match though) but the final output will be a fixed order and field list.
I've setup the 1st part, so I have a UWC transformation with my full table as an input and a variable of the fields they need, it applies this (using a data step and keep) and gives me an output table of just the fields they are entitled to.
The second part I'm struggling with. So far I've tried a data step that sets the output with the full field list and all attribs which is fine, after that I use the below data step hoping it would just map the ones needed into my table and leave the rest as null, it didn't, it errors due to them being missing on the input:
data &_output.;
set &_input.;
run;
However trying to open the output table fails due to all the missing columns, what's needed is for it to do that and set all the unmapped fields to nulls. Essentially the process is:
Full Table > UWC - Filtered view of customer required fields > UWC - Map filtered list back, set non required to null > Full table (which will be exported to a delimited file), we can go straight to that and skip the final table if needed.
Any ideas thanks? Hopefully this helps, the filtered table will be different everytime:
In Table (50 fields):
Name
Address
Postcode
DoB
Colour
John Smith
1 made up street
1MDUS
01/01/2000
Blue
Jane Doe
2 Fake Place
2FAPL
31/12/2005
Red
Filtered view (diff for each supplier and field order can change but names will always match, 1-50 fields)
Postcode
DoB
Name
1MDUS
01/01/2000
John Smith
2FAPL
31/12/2005
Jane Doe
Output (50 fields), can be sas table, ultimately will be a delimited file output):
Name
Address
Postcode
DoB
Colour
John Smith
1MDUS
01/01/2000
Jane Doe
2FAPL
31/12/2005
... View more
