BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
septemberbulb
Obsidian | Level 7

Hello, I am trying to using the data sample (as an example) to created a output below: can anyone help to let me know whether SAS functions can create this output? 

 

Situation: I have a data sample of 100 obs. From the beginning 100 population, I want to calculate the exclusions and remain from each category (as the desire output). The second category start base would be the previous remain in the first category. The view is like the waterfall. The data sample is below as well.

 

Now I am doing this manually in excel and wonder whether in SAS there is any function could let me run it automatically

 

Thank you.

 

Data Sample: 

Client_IDClient_AgeClient_A_ScoreClient_B_ScoreClient_Type
a1185276341
a2946865942
a3424868204
a4185545864
a5824238490
a6654215685
a71004358324
a8454518703
a9894105491
a10205088082
a11635897925
a12347196832
a13287355480
a14765886195
a15534145810
a16337608565
a17316525054
a18764076695
a19376598441
a20403287543
a21317297664
a22856928955
a23843186913
a24466897114
a25683965725
a26535448771
a27225565373
a28563695315
a29867795240
a30826986605
a31907936825
a32277646531
a33233637443
a34954747782
a35973347624
a36704568731
a37264525781
a38436515492
a39286956593
a40303628641
a41443528635
a42937485864
a43793948555
a44213436691
a45457925844
a46955348724
a47345287883
a48814078020
a49634978640
a50924708033
a51643108634
a52343467491
a53506785832
a54994785212
a55483035770
a56295968895
a57256366960
a58996488481
a59745628760
a60666765260
a61277235973
a62547315975
a63546798804
a64624888035
a65793787271
a66214926181
a67207425764
a68495727645
a69197998394
a70326915902
a71405915900
a72297828810
a73524716655
a74245088853
a751003247231
a76416098475
a77947105535
a78515956815
a79825935531
a80795276152
a81995406325
a82725795194
a83337395374
a84456675472
a85927505584
a86205548944
a87487847630
a88697477193
a89476008795
a90507757903
a91787876332
a92844697585
a93874045741
a94683837074
a95953987295
a96433647413
a97924075094
a98786058965
a99936765703
a100747207632

 

Desired Output:

 

 ExcludedWaterfall
Starting Base 100
Keep Age 30-1001981
Keep Client_A_Score>=5003546
Keep Client_B_Score>=6001828
Keep Client Type in (0,1,2)199
End Base 9



1 ACCEPTED SOLUTION

Accepted Solutions
HB
Barite | Level 11 HB
Barite | Level 11

The SQL gods may shoot me but you can cheat it like this if nobody has something more elegant.

 

data clientdata;
	infile cards dlm=" 	";
	input Client_ID:$4. Client_Age  Client_A_Score 
	Client_B_Score 	Client_Type;
datalines;
a1	18	527	634	1
a2	94	686	594	2
a3	42	486	820	4
a4	18	554	586	4
a5	82	423	849	0
a6	65	421	568	5
a7	100	435	832	4
a8	45	451	870	3
a9	89	410	549	1
a10	20	508	808	2
a11	63	589	792	5
a12	34	719	683	2
a13	28	735	548	0
a14	76	588	619	5
a15	53	414	581	0
a16	33	760	856	5
a17	31	652	505	4
a18	76	407	669	5
a19	37	659	844	1
a20	40	328	754	3
a21	31	729	766	4
a22	85	692	895	5
a23	84	318	691	3
a24	46	689	711	4
a25	68	396	572	5
a26	53	544	877	1
a27	22	556	537	3
a28	56	369	531	5
a29	86	779	524	0
a30	82	698	660	5
a31	90	793	682	5
a32	27	764	653	1
a33	23	363	744	3
a34	95	474	778	2
a35	97	334	762	4
a36	70	456	873	1
a37	26	452	578	1
a38	43	651	549	2
a39	28	695	659	3
a40	30	362	864	1
a41	44	352	863	5
a42	93	748	586	4
a43	79	394	855	5
a44	21	343	669	1
a45	45	792	584	4
a46	95	534	872	4
a47	34	528	788	3
a48	81	407	802	0
a49	63	497	864	0
a50	92	470	803	3
a51	64	310	863	4
a52	34	346	749	1
a53	50	678	583	2
a54	99	478	521	2
a55	48	303	577	0
a56	29	596	889	5
a57	25	636	696	0
a58	99	648	848	1
a59	74	562	876	0
a60	66	676	526	0
a61	27	723	597	3
a62	54	731	597	5
a63	54	679	880	4
a64	62	488	803	5
a65	79	378	727	1
a66	21	492	618	1
a67	20	742	576	4
a68	49	572	764	5
a69	19	799	839	4
a70	32	691	590	2
a71	40	591	590	0
a72	29	782	881	0
a73	52	471	665	5
a74	24	508	885	3
a75	100	324	723	1
a76	41	609	847	5
a77	94	710	553	5
a78	51	595	681	5
a79	82	593	553	1
a80	79	527	615	2
a81	99	540	632	5
a82	72	579	519	4
a83	33	739	537	4
a84	45	667	547	2
a85	92	750	558	4
a86	20	554	894	4
a87	48	784	763	0
a88	69	747	719	3
a89	47	600	879	5
a90	50	775	790	3
a91	78	787	633	2
a92	84	469	758	5
a93	87	404	574	1
a94	68	383	707	4
a95	95	398	729	5
a96	43	364	741	3
a97	92	407	509	4
a98	78	605	896	5
a99	93	676	570	3
a100	74	720	763	2
;
run;
proc sql;
	select 1, "Starting base", 0 as Excluded, count(*) as Waterfall
	from clientdata
	union
	select 2, "Keep Age 30-100", 
	count(case when client_age <30 or client_age >100 then 1 end), 
	count(case when client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 3, "Keep Client_A_Score>=500", 
	count(case when client_a_score < 500 and client_age between 30 and 100 then 1 end), 
	count(case when client_a_score >= 500 and client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 4, "Keep Client_B_Score>=600", 
	count(case when client_b_score <600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end), 
	count(case when client_b_score >= 600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 5, "Keep Client Type in (0,1,2)", 
	count(case when client_type not in (0,1,2) 
		and client_b_score >=600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end), 
	count(case when client_type in (0,1,2) 
		and client_b_score >= 600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 6, "End Base", 0 as Excluded,
	count(case when client_type in (0,1,2) 
		and client_b_score >=600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end) 
	from clientdata
	order by 1;
quit;

which gives you

 

    Excluded Waterfall
1 Starting base 0 100
2 Keep Age 30-100 19 81
3 Keep Client_A_Score>=500 35 46
4 Keep Client_B_Score>=600 18 28
5 Keep Client Type in (0,1,2) 19 9
6 End Base 0 9

 

It's ugly.

 

View solution in original post

1 REPLY 1
HB
Barite | Level 11 HB
Barite | Level 11

The SQL gods may shoot me but you can cheat it like this if nobody has something more elegant.

 

data clientdata;
	infile cards dlm=" 	";
	input Client_ID:$4. Client_Age  Client_A_Score 
	Client_B_Score 	Client_Type;
datalines;
a1	18	527	634	1
a2	94	686	594	2
a3	42	486	820	4
a4	18	554	586	4
a5	82	423	849	0
a6	65	421	568	5
a7	100	435	832	4
a8	45	451	870	3
a9	89	410	549	1
a10	20	508	808	2
a11	63	589	792	5
a12	34	719	683	2
a13	28	735	548	0
a14	76	588	619	5
a15	53	414	581	0
a16	33	760	856	5
a17	31	652	505	4
a18	76	407	669	5
a19	37	659	844	1
a20	40	328	754	3
a21	31	729	766	4
a22	85	692	895	5
a23	84	318	691	3
a24	46	689	711	4
a25	68	396	572	5
a26	53	544	877	1
a27	22	556	537	3
a28	56	369	531	5
a29	86	779	524	0
a30	82	698	660	5
a31	90	793	682	5
a32	27	764	653	1
a33	23	363	744	3
a34	95	474	778	2
a35	97	334	762	4
a36	70	456	873	1
a37	26	452	578	1
a38	43	651	549	2
a39	28	695	659	3
a40	30	362	864	1
a41	44	352	863	5
a42	93	748	586	4
a43	79	394	855	5
a44	21	343	669	1
a45	45	792	584	4
a46	95	534	872	4
a47	34	528	788	3
a48	81	407	802	0
a49	63	497	864	0
a50	92	470	803	3
a51	64	310	863	4
a52	34	346	749	1
a53	50	678	583	2
a54	99	478	521	2
a55	48	303	577	0
a56	29	596	889	5
a57	25	636	696	0
a58	99	648	848	1
a59	74	562	876	0
a60	66	676	526	0
a61	27	723	597	3
a62	54	731	597	5
a63	54	679	880	4
a64	62	488	803	5
a65	79	378	727	1
a66	21	492	618	1
a67	20	742	576	4
a68	49	572	764	5
a69	19	799	839	4
a70	32	691	590	2
a71	40	591	590	0
a72	29	782	881	0
a73	52	471	665	5
a74	24	508	885	3
a75	100	324	723	1
a76	41	609	847	5
a77	94	710	553	5
a78	51	595	681	5
a79	82	593	553	1
a80	79	527	615	2
a81	99	540	632	5
a82	72	579	519	4
a83	33	739	537	4
a84	45	667	547	2
a85	92	750	558	4
a86	20	554	894	4
a87	48	784	763	0
a88	69	747	719	3
a89	47	600	879	5
a90	50	775	790	3
a91	78	787	633	2
a92	84	469	758	5
a93	87	404	574	1
a94	68	383	707	4
a95	95	398	729	5
a96	43	364	741	3
a97	92	407	509	4
a98	78	605	896	5
a99	93	676	570	3
a100	74	720	763	2
;
run;
proc sql;
	select 1, "Starting base", 0 as Excluded, count(*) as Waterfall
	from clientdata
	union
	select 2, "Keep Age 30-100", 
	count(case when client_age <30 or client_age >100 then 1 end), 
	count(case when client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 3, "Keep Client_A_Score>=500", 
	count(case when client_a_score < 500 and client_age between 30 and 100 then 1 end), 
	count(case when client_a_score >= 500 and client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 4, "Keep Client_B_Score>=600", 
	count(case when client_b_score <600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end), 
	count(case when client_b_score >= 600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 5, "Keep Client Type in (0,1,2)", 
	count(case when client_type not in (0,1,2) 
		and client_b_score >=600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end), 
	count(case when client_type in (0,1,2) 
		and client_b_score >= 600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end) 
	from clientdata
	union
	select 6, "End Base", 0 as Excluded,
	count(case when client_type in (0,1,2) 
		and client_b_score >=600 
		and client_a_score >=500 
		and client_age between 30 and 100 then 1 end) 
	from clientdata
	order by 1;
quit;

which gives you

 

    Excluded Waterfall
1 Starting base 0 100
2 Keep Age 30-100 19 81
3 Keep Client_A_Score>=500 35 46
4 Keep Client_B_Score>=600 18 28
5 Keep Client Type in (0,1,2) 19 9
6 End Base 0 9

 

It's ugly.

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1 reply
  • 450 views
  • 0 likes
  • 2 in conversation