topic Re: Proc Transpose following a proc SQL SAS EG 7.11 in SAS Enterprise Guide

Proc Transpose following a proc SQL SAS EG 7.11

DME790 — Wed, 24 Aug 2016 03:52:06 GMT

Hi All,

Having some trouble getting a Proc Transpose to work after a Proc SQL

Data example

EVENT_DT	EMPLOYMENT_TYPE	LOGON_ID
Friday, July 01, 2016	Ongoing - Full time	111111111
Friday, July 01, 2016	Ongoing - Full time	111111111
Friday, July 01, 2016	Ongoing - Full time	222222222
Friday, July 01, 2016	Ongoing - Full time	333333333
Friday, July 01, 2016	Ongoing - Full time	333333333
Friday, July 01, 2016	Ongoing - Full time	333333333
Friday, July 01, 2016	Ongoing - Full time	444444444
Friday, July 01, 2016	Ongoing - Full time	444444444
Friday, July 01, 2016	Ongoing - Full time	555555555
Friday, July 01, 2016	Ongoing - Full time	666666666
Friday, July 01, 2016	Ongoing - Full time	666666666
Friday, July 01, 2016	Ongoing - Full time	777777777
Monday, July 04, 2016	Ongoing - Full time	111111111
Monday, July 04, 2016	Ongoing - Full time	111111111
Monday, July 04, 2016	Ongoing - Full time	222222222
Monday, July 04, 2016	Ongoing - Full time	333333333
Monday, July 04, 2016	Ongoing - Full time	333333333
Monday, July 04, 2016	Ongoing - Full time	333333333
Monday, July 04, 2016	Ongoing - Full time	444444444
Monday, July 04, 2016	Ongoing - Full time	444444444
Monday, July 04, 2016	Ongoing - Full time	555555555
Monday, July 04, 2016	Ongoing - Full time	666666666
Monday, July 04, 2016	Ongoing - Full time	666666666
Monday, July 04, 2016	Ongoing - Full time	777777777
Tuesday, July 05, 2016	Ongoing - Full time	111111111
Tuesday, July 05, 2016	Ongoing - Full time	111111111
Tuesday, July 05, 2016	Ongoing - Full time	222222222
Tuesday, July 05, 2016	Ongoing - Full time	333333333
Tuesday, July 05, 2016	Ongoing - Full time	333333333
Tuesday, July 05, 2016	Ongoing - Full time	333333333
Tuesday, July 05, 2016	Ongoing - Full time	444444444
Tuesday, July 05, 2016	Ongoing - Full time	444444444
Tuesday, July 05, 2016	Ongoing - Full time	555555555
Tuesday, July 05, 2016	Ongoing - Full time	666666666
Tuesday, July 05, 2016	Ongoing - Full time	666666666
Tuesday, July 05, 2016	Ongoing - Full time	777777777

Data Report.PST_Unique;
	Set CAR.ET;
	Keep  EVENT_DT EMPLOYMENT_TYPE Logon_ID Count;
	Where EVENT_DT >= '01Jul2016'd
		and CENTRE_TYPE = 'PST'
		and CLUSTER = 'PST - Customer';
	format EVENT_DT WeekDate30.;
Run;

proc sql;
	select EVENT_DT, EMPLOYMENT_TYPE, count(distinct(Logon_ID)) as count
	from Report.PST_Unique
	group by EVENT_DT, EMPLOYMENT_TYPE;
quit;

Proc Transpose data=Report.PST_Unique out=work.PST_UNIQUE_FINAL (DROP=_NAME_);
	By EVENT_DT;
	ID EMPLOYMENT_TYPE;
	VAR Count;
run;

Output from the Proc SQL

Event Date	Employment Type	count
Friday, July 1, 2016	Ongoing - Full time	88
Friday, July 1, 2016	Ongoing - Part time	56
Monday, July 4, 2016	Ongoing - Full time	80
Monday, July 4, 2016	Ongoing - Part time	70
Tuesday, July 5, 2016	Ongoing - Full time	80
Tuesday, July 5, 2016	Ongoing - Part time	62
Wednesday, July 6, 2016	Ongoing - Full time	81
Wednesday, July 6, 2016	Ongoing - Part time	47
Thursday, July 7, 2016	Ongoing - Full time	84
Thursday, July 7, 2016	Ongoing - Part time	57
Friday, July 8, 2016	Ongoing - Full time	74
Friday, July 8, 2016	Ongoing - Part time	53
Monday, July 11, 2016	Ongoing - Full time	75
Monday, July 11, 2016	Ongoing - Part time	63

When the Proc Transpose runs it returns the following error.

42         Proc Transpose data=Report.PST_Unique out=work.PST_UNIQUE_FINAL (DROP=_NAME_);
43         By EVENT_DT;
44         ID EMPLOYMENT_TYPE;
45         VAR Count;
ERROR: Variable COUNT not found.

This is the output I'm trying to get returned

EVENT_DT	Ongoing - Full time	Ongoing - Part time
Friday, July 1, 2016	88	56
Monday, July 4, 2016	80	70
Tuesday, July 5, 2016	80	62
Wednesday, July 6, 2016	81	47
Thursday, July 7, 2016	84	57
Friday, July 8, 2016	74	53
Monday, July 11, 2016	75	63

Also how do I sum Ongoing - Full time and Ongoing - Part time together to get a daily total when using 'distinctive'.

Any help appreciated.

Cheers

Re: Proc Transpose following a proc SQL SAS EG 7.11

RahulG — Wed, 24 Aug 2016 04:07:40 GMT

Your transpose code is correct. I hope COUNT being keyword is not creating any problem, try to use different name for count like count_login_id.

Re: Proc Transpose following a proc SQL SAS EG 7.11

DME790 — Wed, 24 Aug 2016 04:18:51 GMT

Thanks RahulG

I have changed the name and still comes back with ERROR: Variable COUNT_ID not found.

Didn't realise that Count was a keyword so will keep away from using it as a name from now on.

Cheers

Re: Proc Transpose following a proc SQL SAS EG 7.11

RahulG — Wed, 24 Aug 2016 04:26:09 GMT

data input;
input event_Dt employment_type $20. count_id;
format event_dt date9.;
datalines;
152 Ongoing - Full time 34
152 Ongoing - Part time 88
153 Ongoing - Full time 48
153 Ongoing - Part time 45
;
proc transpose data = input out=trans(drop = _name_);
by event_dt;
ID employment_type ;
var count_id;
run;

I tried this code and it works well.

Please check if your dataset after sql is correct.

Re: Proc Transpose following a proc SQL SAS EG 7.11

DME790 — Wed, 24 Aug 2016 04:32:18 GMT

Hi RahulG,

The output after the SQL is right

Event Date	Employment Type	count_ID
Friday, July 1, 2016	Ongoing - Full time	88
Friday, July 1, 2016	Ongoing - Part time	56
Monday, July 4, 2016	Ongoing - Full time	80
Monday, July 4, 2016	Ongoing - Part time	70
Tuesday, July 5, 2016	Ongoing - Full time	80
Tuesday, July 5, 2016	Ongoing - Part time	62
Wednesday, July 6, 2016	Ongoing - Full time	81
Wednesday, July 6, 2016	Ongoing - Part time	47
Thursday, July 7, 2016	Ongoing - Full time	84
Thursday, July 7, 2016	Ongoing - Part time	57

When the Proc Transpose runs that is when I get the error 😞

Re: Proc Transpose following a proc SQL SAS EG 7.11

Reeza — Wed, 24 Aug 2016 04:38:41 GMT

Post your full log.

Re: Proc Transpose following a proc SQL SAS EG 7.11

DME790 — Wed, 24 Aug 2016 04:41:16 GMT

Thanks Reeza - full log

1 The SAS System 10:04 Wednesday, August 24, 2016

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROJECTPATH='R:\NAT\SMARTCENT\SMCTRCHOP\GCFTELE\Telephony Performance\SAS EG Files\PST_ASA_Data.egp';
5          %LET _CLIENTPROJECTNAME='PST_ASA_Data.egp';
6          %LET _SASPROGRAMFILE=;
7
8          ODS _ALL_ CLOSE;
9          OPTIONS DEV=ACTIVEX;
10         GOPTIONS XPIXELS=0 YPIXELS=0;
11         FILENAME EGSR TEMP;
12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13             STYLE=Ocean
14             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/Ocean.css")
15             NOGTITLE
16             NOGFOOTNOTE
17             GPATH=&sasworklocation
18             ENCODING=UTF8
19             options(rolap="on")
20         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22         GOPTIONS ACCESSIBLE;
23         LIBNAME data "/sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/Telephony" access=readonly;
NOTE: Libref DATA refers to the same physical library as CARTEL.
NOTE: Libref DATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/Telephony
24         LIBNAME REPORT "/sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/misc/FAP_OA_Daily";
NOTE: Libref REPORT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/misc/FAP_OA_Daily
25         Run;
26

27         Data Report.PST_Unique;
28         Set CARTEL.Employee_Telephony;
29         Keep EVENT_DT EMPLOYMENT_TYPE Logon_ID;
30         Where EVENT_DT >= '01Jul2016'd
31           and CENTRE_TYPE = 'Smart Centres - Participation Solutions'
32           and CLUSTER = 'PST - Customer';
33         format EVENT_DT WeekDate30.;
34         Run;

NOTE: There were 9295 observations read from the data set CARTEL.EMPLOYEE_TELEPHONY.
      WHERE (EVENT_DT>='01JUL2016'D) and (CENTRE_TYPE='Smart Centres - Participation Solutions') and (CLUSTER='PST - Customer');
NOTE: The data set REPORT.PST_UNIQUE has 9295 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           1.90 seconds
      cpu time            1.18 seconds

35
36         proc sql;
37         select EVENT_DT, EMPLOYMENT_TYPE, count(distinct(Logon_ID)) as count_ID
38           from Report.PST_Unique
39            group by EVENT_DT, EMPLOYMENT_TYPE;
2                                                          The SAS System                           10:04 Wednesday, August 24, 2016

40         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.06 seconds

41
42         Proc Transpose data=Report.PST_Unique out=work.PST_UNIQUE_FINAL (DROP=_NAME_);
43         By EVENT_DT;
44         ID EMPLOYMENT_TYPE;
45         VAR Count_ID;
ERROR: Variable COUNT_ID not found.
46         run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.PST_UNIQUE_FINAL may be incomplete. When this step was stopped there were 0 observations and 0
         variables.
WARNING: Data set WORK.PST_UNIQUE_FINAL was not replaced because this step was stopped.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

47

48 Proc Print data=Report.PST_Unique NOOBS;
49 run;

NOTE: There were 9295 observations read from the data set REPORT.PST_UNIQUE.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           3.97 seconds
      cpu time            4.04 seconds

50
51         Proc SQL;
52         Drop Table Work.PST_UNIQUE_SUM;
WARNING: File WORK.PST_UNIQUE_SUM.DATA does not exist.
WARNING: Table WORK.PST_UNIQUE_SUM has not been dropped.
53         Drop table Report.PST_Unique;
NOTE: Table REPORT.PST_UNIQUE has been dropped.
54         Run;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
55
56         GOPTIONS NOACCESSIBLE;
57         %LET _CLIENTTASKLABEL=;
58         %LET _CLIENTPROJECTPATH=;
59         %LET _CLIENTPROJECTNAME=;
60         %LET _SASPROGRAMFILE=;
61
62         ;*';*";*/;quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

62       !                run;

63 ODS _ALL_ CLOSE;
3 The SAS System 10:04 Wednesday, August 24, 2016

64
65
66         QUIT; RUN;
67

Re: Proc Transpose following a proc SQL SAS EG 7.11

Reeza — Wed, 24 Aug 2016 05:03:27 GMT

The log says it all. Your Proc SQL does not create a table. Add a create a table statement to the top and then pass that dataset to your proc transpose.

Re: Proc Transpose following a proc SQL SAS EG 7.11

DME790 — Wed, 24 Aug 2016 05:12:31 GMT

Thanks Reeza,

Added

proc sql;

	create table PST_Unique_SQL as 
select EVENT_DT, EMPLOYMENT_TYPE, count(distinct(Logon_ID)) as count_ID
		from Report.PST_Unique
			group by EVENT_DT, EMPLOYMENT_TYPE;
quit;

and now get the Transpose to work.

Now how do I create a daily 'Total' column in the final Proc Print?

Re: Proc Transpose following a proc SQL SAS EG 7.11

Reeza — Wed, 24 Aug 2016 05:14:05 GMT

If column you may need Proc tabulate.

If at end maybe sumtotal statements in Proc print.