Hi -
Only new to SAS EG and have other Proc Transpose questions but can't get mine to work. Any help is apprciated
I am trying to transpose some data from
NAME_ Call_Type COL1 COL2 COL3 COL4 COL5
EVENT_DT | Date | 20591 | 20591 | 20591 | 20591 | 20591 |
NATIONAL_NUM | Dialled Number | 111111 | 222222 | 333333 | 444444 | 555555 |
SUCCESS_CONTACT_CNT | Successful Calls | 6768 | 4698 | 11101 | 9427 | 25510 |
BUSY_CONTACT_CNT | Busy | 5516 | 19025 | 21731 | 28727 | 21356 |
to
Call Type AAAA BBBB CCCC DDDD EEEE
Successful Calls 6,768 4,698 11,101 9,427 25,510
Busy 5,516 19,025 21,731 28,727 21,356
without the blank lines of course.
My code at this stage looks like
Data work.CLKSuccessfuldata;
set CARTEL.pre_ivr_call_arrival;
Keep EVENT_DT PROGRAMME_NM NATIONAL_NUM PRE_IVR_QUEUE_DESC SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
Where EVENT_DT>='01Jul2014'd
and EVENT_DT = '17May2016'd and PROGRAMME_NM = '1.1 Social Security & Welfare'
and NATIONAL_NUM in (111111 222222 333333 444444 555555);
Run;
Proc Sort Data=work.CLKSuccessfuldata;
By EVENT_DT PROGRAMME_NM NATIONAL_NUM PRE_IVR_QUEUE_DESC SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
Run;
Proc summary data=work.CLKSuccessfuldata sum noprint;
by EVENT_DT PROGRAMME_NM NATIONAL_NUM PRE_IVR_QUEUE_DESC;
VAR SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
Output out=work.CLKSuccessfulSummarybyDay (drop=_FREQ_ _TYPE_) Sum=;
Run;
Proc Sort data=CLKSuccessfulSummarybyDay_Final;
by EVENT_DT PROGRAMME_NM NATIONAL_NUM PRE_IVR_QUEUE_DESC SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
RUN;
Proc Transpose data=CLKSuccessfulSummarybyDay_Final out=CLKSuccessfulSummarybyDay_Report
label=Call_Type;
Run;
Proc Print NOOBS;
Run;
Thanks in advance
Dean
In the proc transpose use an ID statement as well. This tells SAS to use that variable as the column name.
ID pre_ivr_queue_desc;
Then you can customize your output in a data step.
Data want;
Set clksuccess..;
Rename _label_ = contacts;
Drop _name_;
Format _numeric_ comma12.;
Run;
Hope that helps move you forward!
Your code and data structure don't quite match up to me.
Can you please clarify how your original data is structured and what you want to see as your output.
Hi All
Sorry my first post was a little confusing - only new to SAS and the Community.
This is a an example of my code/output I have used in a Data step
Data work.CLKSuccessfuldata; set CARTEL.pre_ivr_call_arrival; Keep EVENT_DT PROGRAMME_NM NATIONAL_NUM PRE_IVR_QUEUE_DESC SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT; Where EVENT_DT = '19May2016'd and PROGRAMME_NM = 'SSW' and NATIONAL_NUM in (111111 222222 333333 444444 555555) and Queue_Type='CHOPs'; Run;
EVENT_DT | PROGRAMME_NM | NATIONAL_NUM | PRE_IVR_QUEUE_DESC | SUCCESS_CONTACT_CNT | BUSY_CONTACT_CNT |
2016-05-19 | SSW | 111111 | AAAAAA | 100 | 50 |
2016-05-19 | SSW | 222222 | BBBBBBB | 200 | 60 |
2016-05-19 | SSW | 333333 | CCCCCCC | 300 | 70 |
2016-05-19 | SSW | 444444 | DDDDDD | 400 | 80 |
2016-05-19 | SSW | 555555 | EEEEEEEE | 500 | 90 |
2016-05-19 | SSW | 111111 | AAAAAA | 600 | 100 |
2016-05-19 | SSW | 222222 | BBBBBBB | 700 | 110 |
2016-05-19 | SSW | 333333 | CCCCCCC | 800 | 120 |
2016-05-19 | SSW | 444444 | DDDDDD | 900 | 130 |
2016-05-19 | SSW | 555555 | EEEEEEEE | 1000 | 140 |
I have used the following code to transpose the data to get the following result
Proc Sort Data=work.CLKSuccessfuldata;
By PRE_IVR_QUEUE_DESC SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
Run;
Proc summary data=work.CLKSuccessfuldata (Drop=EVENT_DT PROGRAMME_NM NATIONAL_NUM) sum noprint;
by PRE_IVR_QUEUE_DESC;
VAR SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
Output out=work.CLKSuccessfulSummarybyDay_Final (drop=_FREQ_ _TYPE_) Sum=;
Run;
Proc Sort data=CLKSuccessfulSummarybyDay_Final;
by PRE_IVR_QUEUE_DESC SUCCESS_CONTACT_CNT BUSY_CONTACT_CNT;
RUN;
Proc Transpose data=CLKSuccessfulSummarybyDay_Final out=CLKSuccessfulSummarybyDay_Report;
Run;
and I get this result
_NAME_ | _LABEL_ | COL1 | COL2 | COL3 | COL4 | COL5 |
SUCCESS_CONTACT_CNT | Number of Successful Contacts | 8303 | 8373 | 23103 | 5942 | 5858 |
BUSY_CONTACT_CNT | Number with Busy Signal | 17346 | 23183 | 21257 | 3376 | 6694 |
and this is the end result I'm trying to get.
Contacts | AAA | BBB | CCC | DDD | EEE |
Number of Successful Contacts | 8,303 | 8,373 | 23,103 | 5,942 | 5,858 |
Number with Busy Signal | 17,346 | 23,183 | 21,257 | 3,376 | 6,694 |
Thanks heaps
Dean
In the proc transpose use an ID statement as well. This tells SAS to use that variable as the column name.
ID pre_ivr_queue_desc;
Then you can customize your output in a data step.
Data want;
Set clksuccess..;
Rename _label_ = contacts;
Drop _name_;
Format _numeric_ comma12.;
Run;
Hope that helps move you forward!
Hi,
Well, firstly am not reading that code, please try using consistent casing, indetations and such like to make code readable - you can find many examples on here. Looking at the input data you have given and the output data you have given I don;'t see why any of the code is necessary anyways:
data want (keep=event_dt aaaa bbbb ...); set have (where=(event_dt in ("SUCESS_CONTACT_CNT","BUSY_CONTACT_CNT")); aaaa=input(col1,best.); bbbb=input(col2,best.); ... run;
From what I can see anyways. To get a good response, post example test data - in the form of a datastep, and what the output should look like.
If the starting point is the data you show and the desired result is as shown there is no transpose issue.
Assuming your starting data is as shown this provides the desired output:
proc print data=have noobs;
where Call_type in("Successful Calls" "Busy");
var call_type col1-col5;
format col1-col5 comma6.;
label
col1='AAAA'
col2='BBBB'
col3='CCCC'
col4='DDDD'
col5='EEEE'
;
RUN;
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 25. 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.