topic Re: summary count using array in SAS Programming

summary count using array

Eugenio211 — Wed, 09 Mar 2022 16:24:46 GMT

Hello! I wanted to ask assistance with regards to doing summary using array.

below is the data I have and I want to summarize the trans_count using the columns 'Year' and 'Quarter', thanks a lot!

LOCATION	Year	EndQuarter	Trans_Month	Weekday	LOC_DST_TIME	Trans_Count
1	2019	2019Q1	Jan	Tuesday	11 PM	29
2	2019	2019Q1	Jan	Tuesday	12 AM	12
3	2019	2019Q1	Jan	Tuesday	1 AM	11
4	2019	2019Q1	Jan	Tuesday	2 AM	6
5	2019	2019Q1	Jan	Tuesday	3 AM	7
6	2019	2019Q1	Jan	Tuesday	4 AM	7
7	2019	2019Q1	Jan	Tuesday	5 AM	4
8	2019	2019Q1	Jan	Tuesday	6 AM	23
9	2019	2019Q1	Jan	Tuesday	7 AM	25
10	2019	2019Q1	Jan	Tuesday	8 AM	35
11	2019	2019Q1	Jan	Tuesday	9 AM	36
12	2019	2019Q1	Jan	Tuesday	10 AM	70
13	2019	2019Q1	Jan	Tuesday	11 AM	74
14	2019	2019Q1	Jan	Tuesday	12 PM	62
15	2019	2019Q1	Jan	Tuesday	1 PM	73
16	2019	2019Q2	May	Friday	12 PM	110
17	2019	2019Q2	May	Friday	1 PM	112
18	2019	2019Q2	May	Friday	2 PM	126
19	2019	2019Q2	May	Friday	3 PM	146
20	2019	2019Q2	May	Friday	4 PM	155
21	2019	2019Q2	May	Friday	5 PM	148
22	2019	2019Q2	May	Friday	6 PM	165
23	2019	2019Q2	May	Friday	7 PM	122
24	2019	2019Q2	May	Friday	8 PM	108
25	2019	2019Q2	May	Friday	9 PM	67
26	2019	2019Q2	May	Friday	10 PM	75
27	2019	2019Q2	May	Saturday	11 PM	40
28	2019	2019Q2	May	Saturday	12 AM	35
29	2019	2019Q2	May	Saturday	1 AM	18
30	2019	2019Q2	May	Saturday	2 AM	5
31	2019	2019Q2	May	Saturday	3 AM	4
32	2019	2019Q3	Jul	Wednesday	6 PM	28
33	2019	2019Q3	Jul	Wednesday	7 PM	37
34	2019	2019Q3	Jul	Thursday	7 AM	19
35	2019	2019Q3	Jul	Thursday	8 AM	26
36	2019	2019Q3	Jul	Thursday	9 AM	29
37	2019	2019Q3	Jul	Thursday	10 AM	22
38	2019	2019Q3	Jul	Thursday	11 AM	39
39	2019	2019Q3	Jul	Thursday	12 PM	17
40	2019	2019Q3	Jul	Thursday	1 PM	33
41	2019	2019Q3	Jul	Thursday	2 PM	42
42	2019	2019Q3	Jul	Thursday	3 PM	41
43	2019	2019Q4	Dec	Thursday	9 PM	1
44	2019	2019Q4	Dec	Friday	8 AM	1
45	2019	2019Q4	Dec	Friday	9 AM	36
46	2019	2019Q4	Dec	Friday	10 AM	37
47	2019	2019Q4	Dec	Friday	11 AM	57
48	2019	2019Q4	Dec	Friday	12 PM	37
49	2019	2019Q4	Dec	Friday	1 PM	47
50	2019	2019Q4	Dec	Friday	2 PM	45
51	2019	2019Q4	Dec	Friday	3 PM	34
52	2019	2019Q4	Dec	Friday	4 PM	36
53	2019	2019Q4	Dec	Friday	5 PM	36

I want to have the result like below.

LOCATION	Count_2019Q1	Count_2019Q2	Count_2019Q3	Count_2019Q4	Count_2020Q1	Count_2020Q2	Count_2020Q3	Count_2020Q4	Count_2021Q1	Count_2021Q2	Count_2021Q3	Count_2021Q4
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

Re: summary count using array

PaigeMiller — Wed, 09 Mar 2022 16:53:31 GMT

There are no arrays needed here. Please provide the data as SAS data step code, rather than a screen capture (instructions). We can't program to data that is shown in a screen capture. We have asked you for data in this format before, we shouldn't have to ask multiple times, you need to provide the data in this format from now on instead of ignoring the request; you need to do this without fail, and don't wait until we ask. We're trying to help you, but you have to help us.

Also, it seems as if there will be lots of empty cells in your output. For example, in row where LOCATION=1, there is only one value that will appear, that in the COUNT_2019Q1 column, and nowhere else in row where LOCATION=1 will have a value. I wonder why you would choose to do such a thing. Please explain.

Re: summary count using array

ballardw — Wed, 09 Mar 2022 17:05:41 GMT

You need to show what the output you would expect for the given example data might be.

Since you do not show any location=1 with a "quarter" other than 2019Q1 that output looks pretty iffy as a good example.

Using a subset of your data and dropping variables that you do not show in the output, here is a small example you can run for one approach:

data have;
input LOCATION 	Year 	EndQuarter $ Trans_Count;
datalines;
1 	2019 	2019Q1 	 	29
2 	2019 	2019Q1 	 	12
3 	2019 	2019Q1 		11
4 	2019 	2019Q1 		6
5 	2019 	2019Q1 		7
17 	2019 	2019Q2  	112
18 	2019 	2019Q2  	126
19 	2019 	2019Q2  	146
20 	2019 	2019Q2  	155
21 	2019 	2019Q2  	148
22 	2019 	2019Q2  	165
;

proc transpose data=have out=trans (drop=_name_)
   prefix=Count;
   by location;
   id endquarter;
   var trans_count;
run;

If you actually do have multiple values of the same location (NONE SHOWN) then you need to sort the data set by Location before the proc transpose

Are you sure that you need a data set? A report may be nicer if people are reading the output and not used as input to other procedures.

Re: summary count using array

Eugenio211 — Wed, 09 Mar 2022 17:16:17 GMT

hello! Thanks PaigeMiller and ballardw,
below is the data step that I used:
<
data trans2; set trans1
(keep=location rd dm province Trans_Type
T_date Year Weekday Trans_Month Server_Time Local_Time LOC_DST_Time);
EndQuarter=put(T_DATE, yyq.);
run;;>