BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DME790
Pyrite | Level 9

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_DTDate2059120591205912059120591
NATIONAL_NUMDialled Number111111222222333333444444555555
SUCCESS_CONTACT_CNTSuccessful Calls6768469811101942725510
BUSY_CONTACT_CNTBusy551619025217312872721356

 

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

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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!

View solution in original post

6 REPLIES 6
Reeza
Super User

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. 

DME790
Pyrite | Level 9

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_DTPROGRAMME_NMNATIONAL_NUMPRE_IVR_QUEUE_DESCSUCCESS_CONTACT_CNTBUSY_CONTACT_CNT
2016-05-19SSW111111AAAAAA10050
2016-05-19SSW222222BBBBBBB20060
2016-05-19SSW333333CCCCCCC30070
2016-05-19SSW444444DDDDDD40080
2016-05-19SSW555555EEEEEEEE50090
2016-05-19SSW111111AAAAAA600100
2016-05-19SSW222222BBBBBBB700110
2016-05-19SSW333333CCCCCCC800120
2016-05-19SSW444444DDDDDD900130
2016-05-19SSW555555EEEEEEEE1000140

 

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_COL1COL2COL3COL4COL5
SUCCESS_CONTACT_CNTNumber of Successful Contacts830383732310359425858
BUSY_CONTACT_CNTNumber with Busy Signal17346231832125733766694

 

and this is the end result I'm trying to get.

 

ContactsAAABBBCCCDDDEEE
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

Reeza
Super User

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!

DME790
Pyrite | Level 9
Thanks Reeza - worked a treat
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 17847 views
  • 1 like
  • 4 in conversation