SAS - Dynamically create column names using the values from another column

Question

I Have a column with many flags that were parsed from a XML parser. Data looks like this:

USERKEYED=Y;VALMATCH=N;DEVICEVERIFIED=N;EXCEPTION=N;USERREGISTRD=N;ASSOCIATE=Y;EXTERNAL=N;GROSSGIVEN=Y;UMAPPED=N;

I have to create a table with all these column names to capture the flags. Like:

USERKEYED VALMATCH DEVICEVERIFIED EXCEPTION USERREGISTRD ASSOCIATE EXTERNAL GROSSGIVEN UMAPPED 
Y  N N N N Y N Y N 
Y  N N N N Y Y Y N 
Y  N N Y N Y N Y N 

How can I capture values dynamically in SAS? Either in a DATA step or a PROC step?

Thanks in advance.

Answer 1

Let's start with your example output data.

data expect ;
  id+1;
  length USERKEYED VALMATCH DEVICEVERIFIED EXCEPTION 
         USERREGISTRD ASSOCIATE EXTERNAL GROSSGIVEN UMAPPED $1 ;
  input USERKEYED -- UMAPPED;
cards4;
Y  N N N N Y N Y N 
Y  N N N N Y Y Y N 
Y  N N Y N Y N Y N 
;;;;

Now we can recreate your example input data:

data have ;
  do until (last.id);
    set expect ;
    by id ;
    array flag _character_;
    length string $200 ;
    do _n_=1 to dim(flag);
      string=catx(';',string,catx('=',vname(flag(_n_)),flag(_n_)));
    end;
  end;
  keep id string;
run;

Which will look like this:

USERKEYED=Y;VALMATCH=N;DEVICEVERIFIED=N;EXCEPTION=N;USERREGISTRD=N;ASSOCIATE=Y;EXTERNAL=N;GROSSGIVEN=Y;UMAPPED=N 
USERKEYED=Y;VALMATCH=N;DEVICEVERIFIED=N;EXCEPTION=N;USERREGISTRD=N;ASSOCIATE=Y;EXTERNAL=Y;GROSSGIVEN=Y;UMAPPED=N 
USERKEYED=Y;VALMATCH=N;DEVICEVERIFIED=N;EXCEPTION=Y;USERREGISTRD=N;ASSOCIATE=Y;EXTERNAL=N;GROSSGIVEN=Y;UMAPPED=N 

So to process this we need to parse out the pairs from the variable STRING into multiple observations with the individual pairs' values split into NAME and VALUE variables.

data middle ;
  set have ;
  do _n_=1 by 1 while(_n_=1 or scan(string,_n_,';')^=' ');
    length name $32 ;
    name = scan(scan(string,_n_,';'),1,'=');
    value = scan(scan(string,_n_,';'),2,'=');
    output;
  end;
  keep id name value ;
run;

Then we can use PROC TRANSPOSE to convert those observations into variables.

proc transpose data=middle out=want (drop=_name_) ;
  by id;
  id name ;
  var value ;
run;