Desktop productivity for business analysts and programmers

SAS EG Creating a table report

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 108
Accepted Solution

SAS EG Creating a table report

Hi All,

 

I want to create a table that looks like the below from the following. Can I do this from a Proc Tabulate? Or is there a better way of doing it?

 

Data Work.Toowoomba_Transfers;
	set Data.Dataset1;
	Keep WEEK_END_DT Logon NAME ORIGINATING_SKILL TARGET_SKILL_NM TRANSFER_CNT;
	Where WEEK_END_DT >= '26Jun2017.'d 
		and logon_id in ('ADB860' 'ACW710' 'LCM387' 'SGU331' 'CGJ575' 'MMY391' 'JML872' 'CJP922' 'AHN860' 'LSA902' 'TSM178' 'ASO417' 'OCW711' 'RKV507' 'RLP137');
Run;

Data work.ADB860;
	Set work.toowoomba_Transfers;
	Where Logon_ID = 'ADB860';
RUN;

Proc Sort data=work.adb860;
	By WEEK_END_DT ORIGINATING_SKILL TARGET_SKILL_NM;
RUN;

Proc Summary data=work.adb860 sum;
	By WEEK_END_DT ORIGINATING_SKILL TARGET_SKILL_NM;
	VAR TRANSFER_CNT;
	Output out=work.adb860_SUM (DROP=_FREQ_ _TYPE_) SUM=;
RUN;


Proc Tabulate data=work.adb860_sum format=comma9.0;
	class week_end_dt ORIGINATING_SKILL target_skill_nm;
	VAR TRANSFER_CNT;

		Table 
				target_skill_nm = ""
				,week_end_dt = " " *Transfer_cnt=""
				/ box= "Transfers"
				;
				run;

 

data looks like this

WEEK_END_DTORIGINATING_SKILLTARGET_SKILL_NMTRANSFER_CNT
2017-07-16ABC_DSCABC_D137
2017-07-16ABC_DSCABC_E131
2017-07-16ABC_EMSABC_D133
2017-07-16ABC_EMSABC_E133
2017-07-16ABC_EMSABC_R161
2017-07-16ABC_FAOABC_E131
2017-07-16ABC_FAOABC_F241
2017-07-16ABC_FAOABC_F431
2017-07-16ABC_FAOABC_F672
2017-07-16ABC_FAOABC_R181
2017-07-16ABC_FAOABC_Y131
2017-07-16ABC_PAZABC_E131
2017-07-16ABC_RETABC_R181
2017-07-16ABC_TSEABC_E131
2017-07-16ABC_UPDABC_D131
2017-07-16ABC_UPEABC_E131
2017-07-16ABC_YASABC_ALY1
2017-07-16ABC_YASABC_E131
2017-07-16ABC_YASABC_Y111
2017-07-16ABC_YASABC_Y133
2017-07-23ABC_DRUABC_E131
2017-07-23ABC_DSCABC_D132
2017-07-23ABC_EMSABC_E132
2017-07-23ABC_FAOABC_D131
2017-07-23ABC_FAOABC_F241
2017-07-23ABC_TSRABC_R161
2017-07-23ABC_UPDABC_D132
2017-07-23ABC_UPDABC_E131
2017-07-23ABC_UPEABC_E131
2017-07-23ABC_YASABC_E131
2017-07-23ABC_YASABC_Y132
2017-07-30ABC_CIDABC_CID1
2017-07-30ABC_TSPABC_TSP1
2017-08-06ABC_DSCABC_D131
2017-08-06ABC_TSPABC_TSP1
2017-08-20ABC_CIDABC_CID1
2017-08-20ABC_CIDABC_R161
2017-08-20ABC_FAOABC_EAF1
2017-08-20ABC_RETABC_RET1

 

final output to look like this. 

ORIGINATING_SKILLTARGET_SKILL_NM2017-07-162017-07-232017-07-302017-08-062017-08-20
ABC_DSCABC_D1372010
ABC_DSCABC_E1310000
ABC_EMSABC_D1330000
ABC_EMSABC_E1332000
ABC_EMSABC_R1610000
ABC_FAOABC_E1310000
ABC_FAOABC_F2411000
ABC_FAOABC_F4310000
ABC_FAOABC_F6720000
ABC_FAOABC_R1810000

Accepted Solutions
Solution
‎08-21-2017 10:59 PM
Super User
Posts: 22,818

Re: SAS EG Creating a table report

Yes, you can likely simplify this code. 

 

Some ideas:

 

1. Combine the data steps to one. If it's only filtering and keeping variables (WHERE/KEEP) you can do those as a data set option rather than as  a separate step. Why does the IN have all int he first data step and then a subset in the second, are you attempting to automate this for many different values?

2. You don't need a SORT if you don't have a BY statement and I don't see a BY statement in your procs.

3. You may be able to combine the summary and tabulate into one step but I'm not 100% sure of that without trying it, but I'd consider it worth an attempt at least. 

 

Note the sum in the proc tabulate and the reference to your original dataset not the sum data set. I doubt this is going to work as is, but hopefully it gives you an idea. 

 

Proc Tabulate data=work.toowomba_transfers format=comma9.0;
where week_end_dt>= '26Jun017'd and login_id in ('ADB860');

	class week_end_dt ORIGINATING_SKILL target_skill_nm;
	VAR TRANSFER_CNT;

		Table 
				target_skill_nm = ""
				,week_end_dt = " " *Transfer_cnt="" * sum
				/ box= "Transfers"
				;
				run;

View solution in original post


All Replies
Solution
‎08-21-2017 10:59 PM
Super User
Posts: 22,818

Re: SAS EG Creating a table report

Yes, you can likely simplify this code. 

 

Some ideas:

 

1. Combine the data steps to one. If it's only filtering and keeping variables (WHERE/KEEP) you can do those as a data set option rather than as  a separate step. Why does the IN have all int he first data step and then a subset in the second, are you attempting to automate this for many different values?

2. You don't need a SORT if you don't have a BY statement and I don't see a BY statement in your procs.

3. You may be able to combine the summary and tabulate into one step but I'm not 100% sure of that without trying it, but I'd consider it worth an attempt at least. 

 

Note the sum in the proc tabulate and the reference to your original dataset not the sum data set. I doubt this is going to work as is, but hopefully it gives you an idea. 

 

Proc Tabulate data=work.toowomba_transfers format=comma9.0;
where week_end_dt>= '26Jun017'd and login_id in ('ADB860');

	class week_end_dt ORIGINATING_SKILL target_skill_nm;
	VAR TRANSFER_CNT;

		Table 
				target_skill_nm = ""
				,week_end_dt = " " *Transfer_cnt="" * sum
				/ box= "Transfers"
				;
				run;
Frequent Contributor
Posts: 108

Re: SAS EG Creating a table report

Thanks Reeza,

 

I used the following:

 

Data Work.Toowoomba_Transfers_test;
	set work.data;
	Keep WEEK_END_DT logon_id EMPLOYEE_NAME ORIGINATING_SKILL target_skill TRANSFER_CNT;
	Where WEEK_END_DT >= '26Jun2017.'d 
		and logon_id in ('ADB860' 'ACW710' 'LCM387' 'SGU331' 'CGJ575' 'MMY391' 'JML872' 'CJP922' 'AHN860' 'LSA902' 'TSM178' 'ASO417' 'OCW711' 'RKV507' 'RLP137');
Run;



Proc Tabulate data=Work.Toowoomba_Transfers_test format=comma9.0;
where week_end_dt>= '26Jun017'd and logon_id in ('ADB860');

	class week_end_dt ORIGINATING_SKILL target_skill;
	VAR TRANSFER_CNT;

		Table 
				ORIGINATING_SKILL = "" * target_skill = ""
				,week_end_dt = " " *Transfer_cnt="" * sum
				/ box= "Transfers"
				;
				run;

abnd it produced this - Can I get rid of the 'Sum' from displaying?

 

 

 

Transfers16/07/201723/07/201730/07/20176/08/201720/08/2017
SumSumSumSumSum
CLK_CIDCLK_CID..1.1
CLK_R16....1
CLK_DRUCLK_E13.1...
CLK_DSCCLK_D1372.1.
CLK_E131....
CLK_EMSCLK_D133....
CLK_E1332...
CLK_R161....
CLK_FAOCLK_D13.1...
CLK_E131....
CLK_EAF....1
CLK_F2411...
CLK_F431....
CLK_F672....
CLK_R181....
CLK_Y131....
CLK_PAZCLK_E131....
CLK_RETCLK_R181....
CLK_RET....1
CLK_TSECLK_E131....
CLK_TSPCLK_TSP..11.
CLK_TSRCLK_R16.1...
CLK_UPDCLK_D1312...
CLK_E13.1...
CLK_UPECLK_E1311...
CLK_YASCLK_ALY1....
CLK_E1311...
CLK_Y111....
CLK_Y1332...

 

Cheers

Dean

 

 

Super User
Posts: 22,818

Re: SAS EG Creating a table report

Yes, use the same syntax you did for the other variables (sum=' '). 

 

You may also want to look into the MISSTEXT option to get the 0 rather than missing. 

 

And your first data step is unnecessary. The WHERE in PROC TABULATE is the same, so at best it's redundant - you should remove one or the other. 

Frequent Contributor
Posts: 108

Re: SAS EG Creating a table report

Hi Reeza,

 

So the Proc Tabulate doesn't change the original dataset?

 

Can I put an OUTPUT OUT= in a Proc Tabulate?

 

 

Cheers

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 175 views
  • 0 likes
  • 2 in conversation