Hello,
I am trying to rename labels/variables that are different every day based on data pulled if that makes sense.
See below sample:
So since today is 3/21/23, the 3rd column has today's date as the first day and moving across all the way to 3/27/23.
Is there a way to get rid of everything after the date, so instead of "03/21/2023 Tuesday Peak Hour: H" I want just 03/21/2023.
I don't think I can rename, since the dates change, for example when I pull the data tomorrow, the first date will be 03/22/2023.
Hourend_EST | Region | 03/21/2023 Tuesday Peak Hour: H | 03/21/2023 Tuesday Peak Hour: 0 | 03/22/2023 WednesdayPeak Hour: H | 03/22/2023 WednesdayPeak Hour: 0 | 03/23/2023 Thursday Peak Hour: H | 03/23/2023 Thursday Peak Hour: 0 | 03/24/2023 Friday Peak Hour: H | 03/24/2023 Friday Peak Hour: 0 | 03/25/2023 Saturday Peak Hour: H | 03/25/2023 Saturday Peak Hour: 0 | 03/26/2023 Sunday Peak Hour: H | 03/26/2023 Sunday Peak Hour: 0 | 03/27/2023 Monday Peak Hour: H | 03/27/2023 Monday Peak Hour: 0 |
Hour 01 | North | 15.63 | 4.62 | 15.52 | 4.22 | 15.61 | 3.84 | 15.87 | 3.86 | 15.43 | 3.74 | 14.78 | 3.74 | 18.65 | 3.74 |
Hour 01 | Central | 35.43 | 21.54 | 34.71 | 20.67 | 32.26 | 20.29 | 32.06 | 20.7 | 32.14 | 20.31 | 31.07 | 20.27 | 39.43 | 18.52 |
Hour 01 | South | 18.64 | 15.68 | 16.6 | 15.98 | 16.45 | 15.77 | 16.97 | 15.5 | 16.32 | 13.75 | 15.75 | 14.13 | 18.53 | 14.08 |
It would help if we could see the code and a portion of the raw data, but ideally, this should not be a label issue. Putting dates into variable names is never a good idea, and you run into these types of problems.
You really ought to use PROC REPORT on a long data set, and an ACROSS variable, and then the proper label can appear each time without additional effort on your part; this would be done automatically by PROC REPORT.
Example here: Re: Column data into row headers - SAS Support Communities
The sample table is what I pull from a website everyday and import into SAS. The way the data is formatted from the website is how it's seen in the table I posted unfortunately. I was trying to make it easier to understand.
@EyeNeedHelp wrote:
The sample table is what I pull from a website everyday and import into SAS. The way the data is formatted from the website is how it's seen in the table I posted unfortunately. I was trying to make it easier to understand.
I don't understand this.
This is what the data from the website looks like:
Hourend_EST | Region | 03/21/2023 Tuesday Peak Hour: H | 03/21/2023 Tuesday Peak Hour: 0 | 03/22/2023 WednesdayPeak Hour: H | 03/22/2023 WednesdayPeak Hour: 0 | 03/23/2023 Thursday Peak Hour: H | 03/23/2023 Thursday Peak Hour: 0 | 03/24/2023 Friday Peak Hour: H | 03/24/2023 Friday Peak Hour: 0 | 03/25/2023 Saturday Peak Hour: H | 03/25/2023 Saturday Peak Hour: 0 | 03/26/2023 Sunday Peak Hour: H | 03/26/2023 Sunday Peak Hour: 0 | 03/27/2023 Monday Peak Hour: H | 03/27/2023 Monday Peak Hour: 0 |
Hour 01 | North | 15.63 | 4.62 | 15.52 | 4.22 | 15.61 | 3.84 | 15.87 | 3.86 | 15.43 | 3.74 | 14.78 | 3.74 | 18.65 | 3.74 |
Hour 01 | Central | 35.43 | 21.54 | 34.71 | 20.67 | 32.26 | 20.29 | 32.06 | 20.7 | 32.14 | 20.31 | 31.07 | 20.27 | 39.43 | 18.52 |
Hour 01 | South | 18.64 | 15.68 | 16.6 | 15.98 | 16.45 | 15.77 | 16.97 | 15.5 | 16.32 | 13.75 | 15.75 | 14.13 | 18.53 | 14.08 |
Hour 01 | TOTAL | 69.69 | 41.84 | 66.83 | 40.87 | 64.32 | 39.91 | 64.91 | 40.06 | 63.89 | 37.79 | 61.6 | 38.14 | 76.61 | 36.34 |
Hour 02 | North | 15.43 | 4.62 | 15.24 | 4.22 | 15.38 | 3.84 | 15.66 | 3.86 | 15.2 | 3.74 | 14.58 | 3.74 | 18.65 | 3.74 |
Hour 02 | Central | 34.14 | 21.54 | 33.3 | 20.67 | 32.66 | 20.29 | 32.58 | 20.7 | 31.63 | 20.31 | 30.58 | 20.27 | 39.43 | 18.52 |
Hour 02 | South | 18.38 | 15.68 | 16.24 | 15.98 | 16.03 | 15.77 | 16.44 | 15.5 | 15.81 | 13.75 | 15.31 | 14.13 | 18.53 | 14.08 |
Hour 02 | TOTAL | 67.95 | 41.84 | 64.77 | 40.87 | 64.08 | 39.91 | 64.68 | 40.06 | 62.64 | 37.79 | 60.47 | 38.14 | 76.61 | 36.34 |
Hour 03 | North | 15.38 | 4.62 | 15.13 | 4.22 | 15.31 | 3.84 | 15.6 | 3.86 | 15.13 | 3.74 | 14.49 | 3.74 | 18.65 | 3.74 |
Hour 03 | Central | 34.13 | 21.54 | 34.14 | 20.67 | 32.46 | 20.29 | 32.48 | 20.7 | 31.42 | 20.31 | 30.35 | 20.27 | 39.43 | 18.52 |
Hour 03 | South | 18.36 | 15.68 | 16.09 | 15.98 | 15.81 | 15.77 | 16.12 | 15.5 | 15.52 | 13.75 | 15.06 | 14.13 | 18.53 | 14.08 |
Hour 03 | TOTAL | 67.86 | 41.84 | 65.36 | 40.87 | 63.59 | 39.91 | 64.2 | 40.06 | 62.07 | 37.79 | 59.9 | 38.14 | 76.61 | 36.34 |
Hour 04 | North | 15.43 | 4.62 | 15.16 | 4.22 | 15.38 | 3.84 | 15.67 | 3.86 | 15.13 | 3.74 | 14.48 | 3.74 | 18.65 | 3.74 |
Hour 04 | Central | 34.63 | 21.54 | 34.48 | 20.67 | 32.76 | 20.29 | 32.84 | 20.7 | 31.5 | 20.31 | 31.32 | 20.27 | 39.43 | 18.52 |
Hour 04 | South | 18.51 | 15.68 | 16.12 | 15.98 | 15.78 | 15.77 | 16 | 15.5 | 15.37 | 13.75 | 14.92 | 14.13 | 18.53 | 14.08 |
Hour 04 | TOTAL | 68.57 | 41.84 | 65.76 | 40.87 | 63.92 | 39.91 | 64.51 | 40.06 | 62 | 37.79 | 60.71 | 38.14 | 76.61 | 36.34 |
Hour 05 | North | 15.75 | 4.62 | 15.44 | 4.22 | 15.69 | 3.84 | 15.97 | 3.86 | 15.22 | 3.74 | 14.54 | 3.74 | 18.65 | 3.74 |
Hour 05 | Central | 35.14 | 21.56 | 33.75 | 20.69 | 33.88 | 20.31 | 34.1 | 20.7 | 32.02 | 20.31 | 30.77 | 20.27 | 39.43 | 18.52 |
Hour 05 | South | 18.91 | 15.68 | 16.36 | 15.98 | 15.97 | 15.77 | 16.13 | 15.5 | 15.39 | 13.75 | 14.91 | 14.13 | 18.53 | 14.08 |
Hour 05 | TOTAL | 69.8 | 41.86 | 65.55 | 40.89 | 65.54 | 39.93 | 66.2 | 40.06 | 62.63 | 37.79 | 60.23 | 38.14 | 76.61 | 36.34 |
Hour 06 | North | 16.53 | 4.62 | 16.2 | 4.22 | 16.48 | 3.84 | 16.72 | 3.86 | 15.5 | 3.74 | 14.76 | 3.74 | 18.65 | 3.74 |
Hour 06 | Central | 37.67 | 21.56 | 35.94 | 20.69 | 33.92 | 20.31 | 34.39 | 20.7 | 31.93 | 20.31 | 30.47 | 20.27 | 39.43 | 18.55 |
Hour 06 | South | 19.92 | 15.68 | 17.03 | 15.98 | 16.53 | 15.77 | 16.64 | 15.5 | 15.67 | 13.75 | 15.21 | 14.13 | 18.53 | 14.08 |
Hour 06 | TOTAL | 74.12 | 41.86 | 69.17 | 40.89 | 66.93 | 39.93 | 67.75 | 40.06 | 63.1 | 37.79 | 60.44 | 38.14 | 76.61 | 36.37 |
Hour 07 | North | 17.68 | 4.62 | 17.37 | 4.22 | 17.64 | 3.84 | 17.78 | 3.86 | 15.87 | 3.74 | 15.04 | 3.74 | 18.65 | 3.75 |
Hour 07 | Central | 39.99 | 21.56 | 38.2 | 20.69 | 36.05 | 20.31 | 36.77 | 20.7 | 33.2 | 20.31 | 31.3 | 20.27 | 39.43 | 18.55 |
Hour 07 | South | 21.46 | 15.68 | 18.1 | 15.98 | 17.47 | 15.77 | 17.54 | 15.5 | 16.05 | 13.75 | 15.61 | 14.13 | 18.53 | 14.08 |
Hour 07 | TOTAL | 79.13 | 41.86 | 73.67 | 40.89 | 71.16 | 39.93 | 72.08 | 40.06 | 65.12 | 37.79 | 61.95 | 38.14 | 76.61 | 36.38 |
Hour 08 | North | 18.36 | 4.62 | 18.17 | 4.22 | 18.4 | 3.89 | 18.43 | 3.86 | 16.26 | 3.74 | 15.32 | 3.74 | 18.65 | 3.75 |
Hour 08 | Central | 40.89 | 21.56 | 39.38 | 20.59 | 37.42 | 20.49 | 38.13 | 20.7 | 34.38 | 20.31 | 32.11 | 20.27 | 39.43 | 18.55 |
Hour 08 | South | 21.86 | 15.68 | 18.52 | 15.98 | 17.85 | 15.77 | 18.03 | 14.93 | 16.36 | 13.75 | 15.72 | 14.13 | 18.53 | 14.08 |
Hour 08 | TOTAL | 81.12 | 41.86 | 76.07 | 40.79 | 73.67 | 40.16 | 74.58 | 39.49 | 67 | 37.79 | 63.16 | 38.14 | 76.61 | 36.38 |
Hour 09 | North | 18.31 | 4.62 | 18.34 | 4.22 | 18.5 | 3.89 | 18.46 | 3.86 | 16.48 | 3.74 | 15.48 | 3.74 | 18.65 | 3.75 |
Hour 09 | Central | 40.8 | 21.56 | 39.67 | 20.59 | 38.03 | 20.49 | 38.51 | 20.7 | 35.02 | 20.31 | 32.6 | 20.27 | 39.43 | 18.55 |
Hour 09 | South | 21.52 | 15.68 | 18.68 | 15.98 | 18.01 | 15.77 | 18.24 | 14.93 | 16.88 | 13.75 | 16.18 | 14.13 | 18.53 | 14.08 |
Hour 09 | TOTAL | 80.63 | 41.86 | 76.69 | 40.79 | 74.53 | 40.16 | 75.2 | 39.49 | 68.38 | 37.79 | 64.25 | 38.14 | 76.61 | 36.38 |
Hour 10 | North | 18.09 | 4.62 | 18.22 | 4.22 | 18.29 | 3.89 | 18.12 | 3.86 | 16.41 | 3.74 | 15.45 | 3.74 | 18.65 | 3.75 |
Hour 10 | Central | 40.46 | 20.97 | 39.64 | 20.59 | 38.29 | 20.31 | 38.53 | 20.7 | 35.24 | 20.31 | 32.76 | 20.27 | 39.43 | 18.55 |
Hour 10 | South | 20.97 | 15.68 | 18.75 | 15.77 | 18.22 | 15.77 | 18.48 | 14.93 | 17.25 | 13.75 | 16.59 | 14.13 | 18.53 | 14.08 |
Hour 10 | TOTAL | 79.52 | 41.27 | 76.61 | 40.59 | 74.8 | 39.98 | 75.12 | 39.49 | 68.89 | 37.79 | 64.8 | 38.14 | 76.61 | 36.38 |
Hour 11 | North | 17.91 | 4.62 | 18.12 | 4.22 | 18.04 | 3.89 | 17.71 | 3.86 | 16.13 | 3.74 | 15.36 | 3.74 | 18.65 | 3.75 |
Hour 11 | Central | 40.05 | 20.97 | 39.41 | 20.59 | 38.26 | 20.31 | 38.35 | 20.7 | 35.06 | 20.31 | 32.63 | 20.27 | 39.43 | 18.55 |
Hour 11 | South | 20.45 | 15.68 | 18.79 | 15.77 | 18.54 | 15.77 | 18.79 | 14.93 | 17.41 | 13.75 | 16.86 | 14.13 | 18.53 | 14.08 |
Hour 11 | TOTAL | 78.41 | 41.27 | 76.32 | 40.59 | 74.84 | 39.98 | 74.85 | 39.49 | 68.61 | 37.79 | 64.84 | 38.14 | 76.61 | 36.38 |
Hour 12 | North | 17.73 | 4.62 | 18 | 4.22 | 17.8 | 3.89 | 17.34 | 3.86 | 15.84 | 3.74 | 15.26 | 3.74 | 18.65 | 3.75 |
Hour 12 | Central | 39.55 | 20.97 | 39.06 | 20.59 | 38.12 | 20.31 | 38.05 | 20.7 | 34.63 | 20.27 | 32.34 | 20.27 | 39.43 | 18.55 |
Hour 12 | South | 19.93 | 15.68 | 18.8 | 15.77 | 18.93 | 15.77 | 19.04 | 14.93 | 17.52 | 13.75 | 17.03 | 14.13 | 18.53 | 14.08 |
Hour 12 | TOTAL | 77.21 | 41.27 | 75.87 | 40.59 | 74.85 | 39.98 | 74.43 | 39.49 | 67.99 | 37.76 | 64.63 | 38.14 | 76.61 | 36.38 |
Hour 13 | North | 17.57 | 4.62 | 17.87 | 4.22 | 17.56 | 3.89 | 17.02 | 3.86 | 15.57 | 3.74 | 15.16 | 3.74 | 18.65 | 3.75 |
Hour 13 | Central | 39.09 | 20.97 | 38.71 | 20.59 | 37.92 | 20.31 | 37.65 | 20.7 | 34.11 | 20.27 | 32.04 | 20.27 | 39.43 | 18.55 |
Hour 13 | South | 19.43 | 15.68 | 18.86 | 15.77 | 19.33 | 15.77 | 19.25 | 14.93 | 17.57 | 13.75 | 17.15 | 14.13 | 18.53 | 14.08 |
Hour 13 | TOTAL | 76.09 | 41.27 | 75.44 | 40.59 | 74.81 | 39.98 | 73.93 | 39.49 | 67.25 | 37.76 | 64.35 | 38.14 | 76.61 | 36.38 |
Hour 14 | North | 17.47 | 4.62 | 17.74 | 4.22 | 17.37 | 3.89 | 16.76 | 3.86 | 15.29 | 3.74 | 15.03 | 3.74 | 18.65 | 3.75 |
Hour 14 | Central | 38.61 | 20.97 | 38.28 | 20.59 | 37.55 | 20.31 | 37.16 | 20.7 | 33.56 | 20.27 | 31.7 | 20.27 | 39.43 | 18.55 |
Hour 14 | South | 19.02 | 15.68 | 19.07 | 15.77 | 19.79 | 15.77 | 19.48 | 14.93 | 17.7 | 13.75 | 17.31 | 14.13 | 18.53 | 14.08 |
Hour 14 | TOTAL | 75.1 | 41.27 | 75.08 | 40.59 | 74.72 | 39.98 | 73.39 | 39.49 | 66.54 | 37.76 | 64.03 | 38.14 | 76.61 | 36.38 |
Hour 15 | North | 17.32 | 4.62 | 17.55 | 4.22 | 17.16 | 3.89 | 16.5 | 3.79 | 15.09 | 3.74 | 14.95 | 3.74 | 18.65 | 3.75 |
Hour 15 | Central | 37.97 | 20.97 | 37.69 | 20.59 | 37.08 | 20.31 | 36.58 | 20.7 | 33.09 | 20.27 | 31.43 | 20.27 | 39.43 | 18.55 |
Hour 15 | South | 18.65 | 15.68 | 19.26 | 15.77 | 20.24 | 15.77 | 19.55 | 14.93 | 17.84 | 13.75 | 17.43 | 14.13 | 18.53 | 14.08 |
Hour 15 | TOTAL | 73.93 | 41.27 | 74.5 | 40.59 | 74.48 | 39.98 | 72.63 | 39.42 | 66.02 | 37.76 | 63.81 | 38.14 | 76.61 | 36.38 |
Hour 16 | North | 17.22 | 4.62 | 17.43 | 4.22 | 16.98 | 3.89 | 16.29 | 3.79 | 15.03 | 3.74 | 15.02 | 3.74 | 18.65 | 3.75 |
Hour 16 | Central | 37.43 | 20.97 | 37.23 | 20.59 | 36.75 | 20.31 | 36.12 | 20.7 | 32.82 | 20.27 | 31.47 | 20.27 | 39.43 | 18.55 |
Hour 16 | South | 18.41 | 15.68 | 19.41 | 15.77 | 20.61 | 15.77 | 19.59 | 14.93 | 17.98 | 13.75 | 17.59 | 14.13 | 18.53 | 14.08 |
Hour 16 | TOTAL | 73.06 | 41.27 | 74.07 | 40.59 | 74.34 | 39.98 | 72 | 39.42 | 65.83 | 37.76 | 64.07 | 38.14 | 76.61 | 36.38 |
Hour 17 | North | 17.32 | 4.62 | 17.44 | 4.22 | 17.05 | 3.84 | 16.31 | 3.76 | 15.22 | 3.74 | 15.34 | 3.74 | 18.65 | 3.75 |
Hour 17 | Central | 37.31 | 20.97 | 37.11 | 20.59 | 36.7 | 20.31 | 35.96 | 20.7 | 32.85 | 20.27 | 31.85 | 20.27 | 39.43 | 18.55 |
Hour 17 | South | 18.41 | 15.68 | 19.53 | 15.77 | 20.81 | 15.77 | 19.56 | 14.85 | 18.12 | 13.75 | 17.74 | 14.13 | 18.53 | 14.08 |
Hour 17 | TOTAL | 73.04 | 41.27 | 74.07 | 40.59 | 74.57 | 39.93 | 71.83 | 39.32 | 66.19 | 37.76 | 64.93 | 38.14 | 76.61 | 36.38 |
Hour 18 | North | 17.55 | 4.62 | 17.58 | 4.22 | 17.3 | 3.84 | 16.5 | 3.76 | 15.62 | 3.74 | 15.82 | 3.74 | 18.65 | 3.75 |
Hour 18 | Central | 37.55 | 20.97 | 37.19 | 20.59 | 36.83 | 20.31 | 36.03 | 19.9 | 33.14 | 20.27 | 32.4 | 20.27 | 39.43 | 18.55 |
Hour 18 | South | 18.53 | 15.68 | 19.54 | 15.77 | 20.79 | 15.77 | 19.44 | 14.85 | 18.16 | 13.75 | 17.83 | 14.13 | 18.53 | 14.08 |
Hour 18 | TOTAL | 73.62 | 41.27 | 74.31 | 40.59 | 74.92 | 39.93 | 71.97 | 38.52 | 66.91 | 37.76 | 66.05 | 38.14 | 76.61 | 36.38 |
Hour 19 | North | 17.76 | 4.62 | 17.71 | 4.22 | 17.57 | 3.84 | 16.72 | 3.76 | 15.98 | 3.74 | 16.23 | 3.74 | 18.65 | 3.75 |
Hour 19 | Central | 38.17 | 20.97 | 37.42 | 20.59 | 37.07 | 20.31 | 36.32 | 19.9 | 33.69 | 20.27 | 33.17 | 20.27 | 39.43 | 18.55 |
Hour 19 | South | 18.7 | 15.68 | 19.43 | 15.77 | 20.6 | 15.77 | 19.26 | 14.33 | 18.06 | 13.75 | 17.91 | 14.13 | 18.53 | 14.08 |
Hour 19 | TOTAL | 74.62 | 41.27 | 74.56 | 40.59 | 75.24 | 39.93 | 72.3 | 38 | 67.72 | 37.76 | 67.32 | 38.14 | 76.61 | 36.38 |
Hour 20 | North | 17.91 | 4.62 | 17.84 | 3.94 | 17.86 | 3.84 | 16.97 | 3.76 | 16.26 | 3.74 | 16.61 | 3.74 | 18.65 | 3.75 |
Hour 20 | Central | 38.79 | 21.07 | 37.77 | 20.59 | 37.29 | 20.31 | 36.78 | 19.9 | 34.47 | 20.27 | 34.09 | 20.27 | 39.43 | 18.55 |
Hour 20 | South | 19 | 15.68 | 19.45 | 15.77 | 20.49 | 15.77 | 19.21 | 14.33 | 17.95 | 13.75 | 18.25 | 14.13 | 18.53 | 14.08 |
Hour 20 | TOTAL | 75.7 | 41.37 | 75.07 | 40.3 | 75.63 | 39.93 | 72.96 | 38 | 68.68 | 37.76 | 68.96 | 38.14 | 76.61 | 36.38 |
Hour 21 | North | 17.75 | 4.62 | 17.74 | 3.94 | 17.84 | 3.84 | 17.06 | 3.76 | 16.36 | 3.74 | 16.77 | 3.74 | 18.65 | 3.75 |
Hour 21 | Central | 38.09 | 21.05 | 36.96 | 20.57 | 36.63 | 20.29 | 36.2 | 19.9 | 34.21 | 20.27 | 33.68 | 20.27 | 39.43 | 18.55 |
Hour 21 | South | 19.16 | 15.68 | 19.55 | 15.77 | 20.52 | 15.77 | 19.15 | 14.33 | 18.02 | 13.75 | 18.43 | 14.13 | 18.53 | 14.08 |
Hour 21 | TOTAL | 75 | 41.35 | 74.25 | 40.28 | 75 | 39.91 | 72.41 | 38 | 68.59 | 37.76 | 68.88 | 38.14 | 76.61 | 36.38 |
Hour 22 | North | 17.24 | 4.62 | 17.26 | 3.94 | 17.43 | 3.84 | 16.7 | 3.76 | 16.03 | 3.74 | 16.36 | 3.74 | 18.65 | 3.75 |
Hour 22 | Central | 37.7 | 21.05 | 35.49 | 20.57 | 35.21 | 20.29 | 34.94 | 19.9 | 33.24 | 20.27 | 32.62 | 20.27 | 39.43 | 18.55 |
Hour 22 | South | 18.8 | 15.68 | 19.08 | 15.77 | 19.91 | 15.77 | 18.69 | 14.33 | 17.72 | 13.75 | 18.01 | 14.13 | 18.53 | 14.08 |
Hour 22 | TOTAL | 73.74 | 41.35 | 71.83 | 40.28 | 72.56 | 39.91 | 70.34 | 38 | 66.99 | 37.76 | 66.99 | 38.14 | 76.61 | 36.38 |
Hour 23 | North | 16.57 | 4.59 | 16.61 | 3.94 | 16.85 | 3.84 | 16.23 | 3.76 | 15.58 | 3.74 | 15.77 | 3.74 | 18.65 | 3.75 |
Hour 23 | Central | 36.01 | 21.05 | 34.74 | 20.57 | 34.45 | 20.29 | 34.38 | 19.9 | 33.04 | 20.27 | 31.42 | 20.27 | 39.43 | 18.55 |
Hour 23 | South | 18.07 | 15.68 | 18.15 | 15.77 | 18.89 | 15.77 | 17.95 | 14.33 | 17.13 | 13.75 | 17.2 | 14.13 | 18.53 | 14.08 |
Hour 23 | TOTAL | 70.65 | 41.33 | 69.5 | 40.28 | 70.19 | 39.91 | 68.56 | 38 | 65.75 | 37.76 | 64.39 | 38.14 | 76.61 | 36.38 |
Hour 24 | North | 15.97 | 4.59 | 16.02 | 3.84 | 16.27 | 3.84 | 15.81 | 3.76 | 15.14 | 3.74 | 15.26 | 3.74 | 18.65 | 3.75 |
Hour 24 | Central | 35.57 | 21.05 | 33.26 | 20.57 | 32.95 | 20.07 | 33.04 | 19.9 | 31.91 | 20.27 | 31.44 | 20.3 | 39.43 | 18.55 |
Hour 24 | South | 17.24 | 15.68 | 17.17 | 15.77 | 17.81 | 15.77 | 17.08 | 14.33 | 16.4 | 14.16 | 16.26 | 14.13 | 18.53 | 14.08 |
Hour 24 | TOTAL | 68.78 | 41.33 | 66.44 | 40.19 | 67.02 | 39.69 | 65.92 | 38 | 63.45 | 38.17 | 62.97 | 38.17 | 76.61 | 36.38 |
I have no control on how the data is imported into SAS since the original data is what you see above. After I import the above data into a table in SAS, I would like to change the variable names, if that makes more sense?
What are the SAS variable names? I'm not sure why you feel you need to rename or do anything different the next day, as the columns will change, won't they? I'm really not clear on what you are trying to do, I don't grasp the process, I don't grasp the need to change anything.
What you posted looks like an HTML table. Is that what you are reading from the Website? How are you converting the HTML into a SAS dataset? Or are you downloading a text file, such as a CSV file, from the website. If so then show us what the file looks like. It will NOT look like the thing you posted with lines drawn on it. Instead it will just be text.
Assuming you actually do have a CSV file like this:
Hourend_EST,Region,03/21/2023 Tuesday Peak Hour: H,03/21/2023 Tuesday Peak Hour: 0,03/22/2023 WednesdayPeak Hour: H,03/22/2023 WednesdayPeak Hour: 0,03/23/2023 Thursday Peak Hour: H,03/23/2023 Thursday Peak Hour: 0,03/24/2023 Friday Peak Hour: H,03/24/2023 Friday Peak Hour: 0,03/25/2023 Saturday Peak Hour: H,03/25/2023 Saturday Peak Hour: 0,03/26/2023 Sunday Peak Hour: H,03/26/2023 Sunday Peak Hour: 0,03/27/2023 Monday Peak Hour: H,03/27/2023 Monday Peak Hour: 0
Hour 01,North,15.63,4.62,15.52,4.22,15.61,3.84,15.87,3.86,15.43,3.74,14.78,3.74,18.65,3.74 Hour 01,Central,35.43,21.54,34.71,20.67,32.26,20.29,32.06,20.7,32.14,20.31,31.07,20.27,39.43,18.52 Hour 01,South,18.64,15.68,16.6,15.98,16.45,15.77,16.97,15.5,16.32,13.75,15.75,14.13,18.53,14.08 Hour 01,TOTAL,69.69,41.84,66.83,40.87,64.32,39.91,64.91,40.06,63.89,37.79,61.6,38.14,76.61,36.34
You can easily read the file directly and read the header row also.
data information;
infile 'website.csv' dsd truncover firstobs=2;
input hour :$9. region :$7. value1-value14;
run;
proc transpose data=information(obs=0) out=names;
var _all_;
run;
data names;
set names ;
infile 'website.csv' dsd obs=1 truncover;
input label :$256. @@ ;
run;
You could then if you want use that data to attach labels to the variables. Or parse the labels and extract the date.
You might even be able to do it all in one step:
data want;
infile 'c:\downloads\website.csv' dsd truncover;
length dummy $50 hourend 8 region $7 day 8 date 8 dow $9 peakhour $1 value 8;
array _date [14] _temporary_;
array _dow [14] $9 _temporary_;
array _hour [14] $1 _temporary_;
if _n_=1 then do;
input dummy dummy @;
do day=1 to 14 ;
input dummy @ ;
_date[day] = input(dummy,mmddyy10.);
_dow[day] = scan(dummy,2,' ');
_hour[day] = char(dummy,length(dummy));
end;
input;
end;
input dummy region @;
hourend = input(scan(dummy,-1,' '),32.);
do day=1 to 14;
input value @;
date = _date[day];
dow = _dow[day];
peakhour = _hour[day];
output;
end;
drop dummy;
format date yymmdd10.;
run;
Result:
Which you could re-print into a similar table as what you showed in your question with a simple PROC REPORT step.
proc report ;
column hourend region value,date,peakhour;
define hourend/group;
define region/group;
define date / across ' ';
define peakhour/ across ' ';
define value / sum ' ';
run;
First and editorial comment: GACK!!!
Second, if today is 3/21/2023 (really, get in the habit of using 4-digit years when discussing programming problems, you will at some point save a lot of headaches), how do they have values for 3/27/2023??
Are there always the same number of columns?
Do you have to combine these daily data sets?
If the file is standard enough that it always has the same number of columns and two "peakk" values I would read it something like this:
/*Hourend_EST Region 03/21/2023 Tuesday Peak Hour: H 03/21/2023 Tuesday Peak Hour: 0 03/22/2023 WednesdayPeak Hour: H 03/22/2023 WednesdayPeak Hour: 0 03/23/2023 Thursday Peak Hour: H 03/23/2023 Thursday Peak Hour: 0 03/24/2023 Friday Peak Hour: H 03/24/2023 Friday Peak Hour: 0 03/25/2023 Saturday Peak Hour: H 03/25/2023 Saturday Peak Hour: 0 03/26/2023 Sunday Peak Hour: H 03/26/2023 Sunday Peak Hour: 0 03/27/2023 Monday Peak Hour: H 03/27/2023 Monday Peak Hour: 0*/ data example; infile datalines; /* really would be more like*/ /* infile yourfile firstobs=2 ; */ input dummy $ hour Region $ @; do date = '21MAR2023'd to '27Mar2023'd; input Peak_H Peak_0 @; output; end; format date yymmdd10.; drop dummy; input; datalines; Hour 01 North 15.63 4.62 15.52 4.22 15.61 3.84 15.87 3.86 15.43 3.74 14.78 3.74 18.65 3.74 Hour 01 Central 35.43 21.54 34.71 20.67 32.26 20.29 32.06 20.7 32.14 20.31 31.07 20.27 39.43 18.52 Hour 01 South 18.64 15.68 16.6 15.98 16.45 15.77 16.97 15.5 16.32 13.75 15.75 14.13 18.53 14.08 Hour 01 TOTAL 69.69 41.84 66.83 40.87 64.32 39.91 64.91 40.06 63.89 37.79 61.6 38.14 76.61 36.34 Hour 02 North 15.43 4.62 15.24 4.22 15.38 3.84 15.66 3.86 15.2 3.74 14.58 3.74 18.65 3.74 Hour 02 Central 34.14 21.54 33.3 20.67 32.66 20.29 32.58 20.7 31.63 20.31 30.58 20.27 39.43 18.52 Hour 02 South 18.38 15.68 16.24 15.98 16.03 15.77 16.44 15.5 15.81 13.75 15.31 14.13 18.53 14.08 Hour 02 TOTAL 67.95 41.84 64.77 40.87 64.08 39.91 64.68 40.06 62.64 37.79 60.47 38.14 76.61 36.34 Hour 03 North 15.38 4.62 15.13 4.22 15.31 3.84 15.6 3.86 15.13 3.74 14.49 3.74 18.65 3.74 Hour 03 Central 34.13 21.54 34.14 20.67 32.46 20.29 32.48 20.7 31.42 20.31 30.35 20.27 39.43 18.52 ;
Then each observation is a Date, peak, region and hour combination. And the Long form would be more likely to be usable for reports, as in you wouldn't be changing variable names every single day for every single report as your proposed changing variable names to reflect the date would require.
Actually, I might be tempted to drop the TOTAL value for region as well as that is apparently the Sum of the other values for the hour could be generated as needed.
Sorry, I should have included more detail.
This is a forecast, that is why there's values for 03/27/2023.
There will always be the same number of columns every day.
No, I don't need to combine them. It is used as a forecast and tomorrow there will be a new forecast.
I might just have to leave it how it is.
@EyeNeedHelp wrote:
Sorry, I should have included more detail.
This is a forecast, that is why there's values for 03/27/2023.
There will always be the same number of columns every day.
No, I don't need to combine them. It is used as a forecast and tomorrow there will be a new forecast.
I might just have to leave it how it is.
So what do you actually do with this data set after it is read?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.