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?
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;
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.
Brilliant, thank you very much, that has helped massively
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: