Creating a new variable based on criteria of hit or miss

[darren173]

I have a dataset that is pulling call data from another system in a csv file.  The data set has about 100 variables, that all list what the call is about with either a hit or a miss depending on whether the language is picked up in the call.  It looks similar to below:

Call ID     Lapse       Price Query       Accept         Change of Car        Change of Address    ..............      

1               Hit                Miss                Miss                Miss                          Miss

2              Miss              Miss                 Hit                   Hit                            Miss

3              Miss               Hit                  Miss                Miss                          Miss

What I am trying to do is create several new variables at the end, which return the variable header if that call hits the category so rather than have data from 100 variables I can condense it down to 3 or 4 (a call shouldn't ever hit more than 2-3 categories), so the end result would look similar to this 

Call ID     Lapse       Price Query       Accept         Change of Car        Change of Address         Cat 1                   Cat 2     

1               Hit                Miss                Miss                Miss                          Miss                        Lapse         

2              Miss              Miss                 Hit                   Hit                            Miss                        Accept             Change of Car         

3              Miss               Hit                  Miss                Miss                          Miss                    Price Query

Can anyone help with what syntax I would need to write to achieve this?

[andreas_lds]

This may sound strange, but i would not create multiple variables, but multiple obs for each Call_ID.

data have;
   length Call_ID Lapse Price_Query Accept Change_of_Car Change_of_Address $ 4;
   input Call_ID Lapse Price_Query Accept Change_of_Car Change_of_Address;
   datalines;
1 Hit Miss Miss Miss Miss
2 Miss Miss Hit Hit Miss
3 Miss Hit Miss Miss Miss
;

proc transpose data=have out=want(where=(Col1 = 'Hit')) name=Category;
   by Call_ID;
   var Lapse--Change_of_Address;
run;

[PaigeMiller]

Great point, @andreas_lds .

I agree that in just about any scenario, the layout of data from your PROC TRANSPOSE would be superior to what was originally suggested.

[darren173]

Brilliant, thank you very much, that has helped massively