topic Re: Proc Transpose Remove Obs and rename COL1 etc SAS 7.11 in SAS Enterprise Guide

Proc Transpose Remove Obs and rename COL1 etc SAS 7.11

DME790 — Thu, 19 May 2016 03:29:09 GMT

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

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

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

Reeza — Thu, 19 May 2016 05:24:48 GMT

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.

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

RW9 — Thu, 19 May 2016 08:45:28 GMT

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.

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

ballardw — Thu, 19 May 2016 16:09:50 GMT

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;

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

DME790 — Fri, 20 May 2016 06:11:21 GMT

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

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

Reeza — Fri, 20 May 2016 08:21:28 GMT

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!

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

DME790 — Wed, 25 May 2016 02:46:04 GMT

Thanks Reeza - worked a treat