topic Re: Creating a new variable based on criteria of hit or miss in SAS Enterprise Guide

Creating a new variable based on criteria of hit or miss

darren173 — Mon, 29 Mar 2021 11:38:09 GMT

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?

Re: Creating a new variable based on criteria of hit or miss

andreas_lds — Mon, 29 Mar 2021 13:21:52 GMT

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;

Re: Creating a new variable based on criteria of hit or miss

PaigeMiller — Mon, 29 Mar 2021 13:30:52 GMT

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.

Re: Creating a new variable based on criteria of hit or miss

darren173 — Tue, 30 Mar 2021 15:02:47 GMT

Brilliant, thank you very much, that has helped massively