Desktop productivity for business analysts and programmers

Proc Transpose following a proc SQL SAS EG 7.11

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

Proc Transpose following a proc SQL SAS EG 7.11

Hi All,

 

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

 

Data example

 

EVENT_DTEMPLOYMENT_TYPELOGON_ID
Friday, July 01, 2016Ongoing - Full time111111111
Friday, July 01, 2016Ongoing - Full time111111111
Friday, July 01, 2016Ongoing - Full time222222222
Friday, July 01, 2016Ongoing - Full time333333333
Friday, July 01, 2016Ongoing - Full time333333333
Friday, July 01, 2016Ongoing - Full time333333333
Friday, July 01, 2016Ongoing - Full time444444444
Friday, July 01, 2016Ongoing - Full time444444444
Friday, July 01, 2016Ongoing - Full time555555555
Friday, July 01, 2016Ongoing - Full time666666666
Friday, July 01, 2016Ongoing - Full time666666666
Friday, July 01, 2016Ongoing - Full time777777777
Monday, July 04, 2016Ongoing - Full time111111111
Monday, July 04, 2016Ongoing - Full time111111111
Monday, July 04, 2016Ongoing - Full time222222222
Monday, July 04, 2016Ongoing - Full time333333333
Monday, July 04, 2016Ongoing - Full time333333333
Monday, July 04, 2016Ongoing - Full time333333333
Monday, July 04, 2016Ongoing - Full time444444444
Monday, July 04, 2016Ongoing - Full time444444444
Monday, July 04, 2016Ongoing - Full time555555555
Monday, July 04, 2016Ongoing - Full time666666666
Monday, July 04, 2016Ongoing - Full time666666666
Monday, July 04, 2016Ongoing - Full time777777777
Tuesday, July 05, 2016Ongoing - Full time111111111
Tuesday, July 05, 2016Ongoing - Full time111111111
Tuesday, July 05, 2016Ongoing - Full time222222222
Tuesday, July 05, 2016Ongoing - Full time333333333
Tuesday, July 05, 2016Ongoing - Full time333333333
Tuesday, July 05, 2016Ongoing - Full time333333333
Tuesday, July 05, 2016Ongoing - Full time444444444
Tuesday, July 05, 2016Ongoing - Full time444444444
Tuesday, July 05, 2016Ongoing - Full time555555555
Tuesday, July 05, 2016Ongoing - Full time666666666
Tuesday, July 05, 2016Ongoing - Full time666666666
Tuesday, July 05, 2016Ongoing - Full time777777777

 

 

 

 

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 DateEmployment Typecount
Friday, July 1, 2016Ongoing - Full time88
Friday, July 1, 2016Ongoing - Part time56
Monday, July 4, 2016Ongoing - Full time80
Monday, July 4, 2016Ongoing - Part time70
Tuesday, July 5, 2016Ongoing - Full time80
Tuesday, July 5, 2016Ongoing - Part time62
Wednesday, July 6, 2016Ongoing - Full time81
Wednesday, July 6, 2016Ongoing - Part time47
Thursday, July 7, 2016Ongoing - Full time84
Thursday, July 7, 2016Ongoing - Part time57
Friday, July 8, 2016Ongoing - Full time74
Friday, July 8, 2016Ongoing - Part time53
Monday, July 11, 2016Ongoing - Full time75
Monday, July 11, 2016Ongoing - Part time63

 

 

 

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_DTOngoing - Full timeOngoing - Part time
Friday, July 1, 20168856
Monday, July 4, 20168070
Tuesday, July 5, 20168062
Wednesday, July 6, 20168147
Thursday, July 7, 20168457
Friday, July 8, 20167453
Monday, July 11, 20167563

 

 

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


Accepted Solutions
Solution
‎08-24-2016 01:12 AM
Grand Advisor
Posts: 17,464

Re: Proc Transpose following a proc SQL SAS EG 7.11

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. 

View solution in original post


All Replies
Regular Contributor
Posts: 236

Re: Proc Transpose following a proc SQL SAS EG 7.11

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.

 

 

Contributor
Posts: 48

Re: Proc Transpose following a proc SQL SAS EG 7.11

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

Regular Contributor
Posts: 236

Re: Proc Transpose following a proc SQL SAS EG 7.11

 

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.

Contributor
Posts: 48

Re: Proc Transpose following a proc SQL SAS EG 7.11

Hi RahulG,

 

The output after the SQL is right

 

Event DateEmployment Typecount_ID
Friday, July 1, 2016Ongoing - Full time88
Friday, July 1, 2016Ongoing - Part time56
Monday, July 4, 2016Ongoing - Full time80
Monday, July 4, 2016Ongoing - Part time70
Tuesday, July 5, 2016Ongoing - Full time80
Tuesday, July 5, 2016Ongoing - Part time62
Wednesday, July 6, 2016Ongoing - Full time81
Wednesday, July 6, 2016Ongoing - Part time47
Thursday, July 7, 2016Ongoing - Full time84
Thursday, July 7, 2016Ongoing - Part time57

 

When the Proc Transpose runs that is when I get the error Smiley Sad

Grand Advisor
Posts: 17,464

Re: Proc Transpose following a proc SQL SAS EG 7.11

Post your full log. 

Contributor
Posts: 48

Re: Proc Transpose following a proc SQL SAS EG 7.11

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        
Solution
‎08-24-2016 01:12 AM
Grand Advisor
Posts: 17,464

Re: Proc Transpose following a proc SQL SAS EG 7.11

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. 

Contributor
Posts: 48

Re: Proc Transpose following a proc SQL SAS EG 7.11

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? 

Grand Advisor
Posts: 17,464

Re: Proc Transpose following a proc SQL SAS EG 7.11

If column you may need Proc tabulate. 

If at end maybe sumtotal statements in Proc print. 

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 446 views
  • 1 like
  • 3 in conversation