BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DME790
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
Reeza
Super User

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;
DME790
Pyrite | Level 9

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

 

 

Reeza
Super User

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. 

DME790
Pyrite | Level 9

Hi Reeza,

 

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

 

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

 

 

Cheers

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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