Set unmapped fields to null/different fields on input to output?

[MRDM]

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

[TomKari]

Could you share the important bits of the code for one supplier, as if you weren't trying to generalize it? That'll give us an idea of what the basic coding looks like.

Tom

[Tom · Accepted solution]

I don't understand how you know which fields a supplier gets.

Do you have that in a macro variable? Or can you get it into a macro variable?

Assume the list is in the macro variable VARLIST.  Like this:

%let varlist=Postcode DoB Name;

Now you can write a step like this to create a dataset with ALL of the variable but only populate the ones you want.

data &_output.;
  set &_input.(obs=0) &_input.(keep=&varlist.);
run;

[MRDM]

I tried your code and it worked, that's great thanks, so it seems the only thing I was missing was on my first UWC and my second UWC isn't needed. What does the below do to make it work (I know obs=0 creates the empty dataset)? How does that solve the mapping issue?

Thanks

&_input.(obs=0) 

For reference my code is below:

&supplier_fields. is a variable from a reference table created by a loop which loops through each supplier and creates the variable for their selected fields.

UWC1 (works to make filtered list of just the selected fields) 

data &_output.;
set &_input.
(keep = &supplier_fields.);
run;

UWC 2, doesn't work, my attempt to map the few to the many:

DATA  &_output.
         (keep = NAME ADDRESS POSTCODE DOB COLOUR )
      ; 
   
   attrib NAME length=$25 format=$25. label="NAME"
          ADDRESS length=$50 format=$50. label="ADDRESS"
          POSTCODE length=$8 format=$20. label="POSTCODE"
          DOB length=8 format=DATETIME26.7 label="DOB"
          COLOUR length=$25 format=$25. label="COLOUR"
          ; 
run;

data &_output.;
set &_input.;
run;

[Tom]

The data step compiler defines the variables essentially by the first reference to them.  By adding the extra reference to the full dataset we insure that is the first place they are seen.  By adding the OBS=0 dataset option when the step actually runs no records are actually read from that reference to the dataset. Instead the one with the KEEEP= option is the one where the values are read, but because of the KEEP= option the values form the unnamed variables are not copied in.

Your attempt does not work because you are running TWO data steps.  So the second one just overwrites the first one, so effectively it is ignored.  Also you appear to be trying to do the opposite of what you described.  By adding the KEEP= option on your OUTPUT dataset, that will only keep the listed variables, instead of keeping the super set of variables. You could collapse them into one data step.  But it is not clear it is really want you want since you want the extra variable kept.  This type of logic would work if INPUT does NOT have the extra variables and you want to add the ATTRIB statement so that those variables are defined so they can be written to OUTPUT.

DATA  &_output.; 
  attrib NAME length=$25 format=$25. label="NAME"
          ADDRESS length=$50 format=$50. label="ADDRESS"
          POSTCODE length=$8 format=$20. label="POSTCODE"
          DOB length=8 format=DATETIME26.7 label="DOB"
          COLOUR length=$25 format=$25. label="COLOUR"
          ; 
  set &_input.;
run;

PS You seem to have an extra U in that last variable name.  🙂 

[Patrick]

If you always want the same structure then just define a mapping table with all the columns but no rows. Then in the data step where you create the actual table use the mapping table at the beginning - this will create the PDV with the variable names and attributes you want (it's first comes first served with SAS so same named variables in later input tables won't overwrite already defined variables in the PDV).

Below sample code to illustrate what I mean.

data mapping;
  stop;
  attrib 
    colA length=8 format=best32.
    colB length=$5 label='some label'
    ;
run;

data sup1;
  attrib colB label="label you don't want";
  colB='abc';
run;

data out_sup1;
  if 0 then set mapping;
  set sup1;
run;

proc contents data=out_sup1;
run;quit;

[MRDM]

Thanks for the replies, Tom's first solution worked fine, just adding the "&_input.(obs=0)". It creates the output file with all fields but only populates the ones I specify in keep= with data which is perfect.