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 |
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.