Desktop productivity for business analysts and programmers

Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

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

 

 

 

 

 

 

 

 


Accepted Solutions
Solution
‎05-24-2016 10:45 PM
Grand Advisor
Posts: 17,383

Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

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


All Replies
Grand Advisor
Posts: 17,383

Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

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. 

Contributor
Posts: 48

Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

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

Solution
‎05-24-2016 10:45 PM
Grand Advisor
Posts: 17,383

Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

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!

Contributor
Posts: 48

Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

Thanks Reeza - worked a treat
Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

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.

Grand Advisor
Posts: 10,215

Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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