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;
... View more
