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
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.
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.
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
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.
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 😞
Post your full log.
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.
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?
If column you may need Proc tabulate.
If at end maybe sumtotal statements in Proc print.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.