BookmarkSubscribeRSS Feed
EyeNeedHelp
Calcite | Level 5

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_ESTRegion03/21/2023 Tuesday  Peak Hour: H03/21/2023 Tuesday  Peak Hour: 003/22/2023 WednesdayPeak Hour: H03/22/2023 WednesdayPeak Hour: 003/23/2023 Thursday Peak Hour: H03/23/2023 Thursday Peak Hour: 003/24/2023 Friday   Peak Hour: H03/24/2023 Friday   Peak Hour: 003/25/2023 Saturday Peak Hour: H03/25/2023 Saturday Peak Hour: 003/26/2023 Sunday   Peak Hour: H03/26/2023 Sunday   Peak Hour: 003/27/2023 Monday   Peak Hour: H03/27/2023 Monday   Peak Hour: 0
Hour   01North15.634.6215.524.2215.613.8415.873.8615.433.7414.783.7418.653.74
Hour   01Central35.4321.5434.7120.6732.2620.2932.0620.732.1420.3131.0720.2739.4318.52
Hour   01South18.6415.6816.615.9816.4515.7716.9715.516.3213.7515.7514.1318.5314.08
9 REPLIES 9
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
EyeNeedHelp
Calcite | Level 5

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.

PaigeMiller
Diamond | Level 26

@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.

--
Paige Miller
EyeNeedHelp
Calcite | Level 5

This is what the data from the website looks like:

 

Hourend_ESTRegion03/21/2023 Tuesday  Peak Hour: H03/21/2023 Tuesday  Peak Hour: 003/22/2023 WednesdayPeak Hour: H03/22/2023 WednesdayPeak Hour: 003/23/2023 Thursday Peak Hour: H03/23/2023 Thursday Peak Hour: 003/24/2023 Friday   Peak Hour: H03/24/2023 Friday   Peak Hour: 003/25/2023 Saturday Peak Hour: H03/25/2023 Saturday Peak Hour: 003/26/2023 Sunday   Peak Hour: H03/26/2023 Sunday   Peak Hour: 003/27/2023 Monday   Peak Hour: H03/27/2023 Monday   Peak Hour: 0
Hour   01North15.634.6215.524.2215.613.8415.873.8615.433.7414.783.7418.653.74
Hour   01Central35.4321.5434.7120.6732.2620.2932.0620.732.1420.3131.0720.2739.4318.52
Hour   01South18.6415.6816.615.9816.4515.7716.9715.516.3213.7515.7514.1318.5314.08
Hour   01TOTAL69.6941.8466.8340.8764.3239.9164.9140.0663.8937.7961.638.1476.6136.34
Hour   02North15.434.6215.244.2215.383.8415.663.8615.23.7414.583.7418.653.74
Hour   02Central34.1421.5433.320.6732.6620.2932.5820.731.6320.3130.5820.2739.4318.52
Hour   02South18.3815.6816.2415.9816.0315.7716.4415.515.8113.7515.3114.1318.5314.08
Hour   02TOTAL67.9541.8464.7740.8764.0839.9164.6840.0662.6437.7960.4738.1476.6136.34
Hour   03North15.384.6215.134.2215.313.8415.63.8615.133.7414.493.7418.653.74
Hour   03Central34.1321.5434.1420.6732.4620.2932.4820.731.4220.3130.3520.2739.4318.52
Hour   03South18.3615.6816.0915.9815.8115.7716.1215.515.5213.7515.0614.1318.5314.08
Hour   03TOTAL67.8641.8465.3640.8763.5939.9164.240.0662.0737.7959.938.1476.6136.34
Hour   04North15.434.6215.164.2215.383.8415.673.8615.133.7414.483.7418.653.74
Hour   04Central34.6321.5434.4820.6732.7620.2932.8420.731.520.3131.3220.2739.4318.52
Hour   04South18.5115.6816.1215.9815.7815.771615.515.3713.7514.9214.1318.5314.08
Hour   04TOTAL68.5741.8465.7640.8763.9239.9164.5140.066237.7960.7138.1476.6136.34
Hour   05North15.754.6215.444.2215.693.8415.973.8615.223.7414.543.7418.653.74
Hour   05Central35.1421.5633.7520.6933.8820.3134.120.732.0220.3130.7720.2739.4318.52
Hour   05South18.9115.6816.3615.9815.9715.7716.1315.515.3913.7514.9114.1318.5314.08
Hour   05TOTAL69.841.8665.5540.8965.5439.9366.240.0662.6337.7960.2338.1476.6136.34
Hour   06North16.534.6216.24.2216.483.8416.723.8615.53.7414.763.7418.653.74
Hour   06Central37.6721.5635.9420.6933.9220.3134.3920.731.9320.3130.4720.2739.4318.55
Hour   06South19.9215.6817.0315.9816.5315.7716.6415.515.6713.7515.2114.1318.5314.08
Hour   06TOTAL74.1241.8669.1740.8966.9339.9367.7540.0663.137.7960.4438.1476.6136.37
Hour   07North17.684.6217.374.2217.643.8417.783.8615.873.7415.043.7418.653.75
Hour   07Central39.9921.5638.220.6936.0520.3136.7720.733.220.3131.320.2739.4318.55
Hour   07South21.4615.6818.115.9817.4715.7717.5415.516.0513.7515.6114.1318.5314.08
Hour   07TOTAL79.1341.8673.6740.8971.1639.9372.0840.0665.1237.7961.9538.1476.6136.38
Hour   08North18.364.6218.174.2218.43.8918.433.8616.263.7415.323.7418.653.75
Hour   08Central40.8921.5639.3820.5937.4220.4938.1320.734.3820.3132.1120.2739.4318.55
Hour   08South21.8615.6818.5215.9817.8515.7718.0314.9316.3613.7515.7214.1318.5314.08
Hour   08TOTAL81.1241.8676.0740.7973.6740.1674.5839.496737.7963.1638.1476.6136.38
Hour   09North18.314.6218.344.2218.53.8918.463.8616.483.7415.483.7418.653.75
Hour   09Central40.821.5639.6720.5938.0320.4938.5120.735.0220.3132.620.2739.4318.55
Hour   09South21.5215.6818.6815.9818.0115.7718.2414.9316.8813.7516.1814.1318.5314.08
Hour   09TOTAL80.6341.8676.6940.7974.5340.1675.239.4968.3837.7964.2538.1476.6136.38
Hour   10North18.094.6218.224.2218.293.8918.123.8616.413.7415.453.7418.653.75
Hour   10Central40.4620.9739.6420.5938.2920.3138.5320.735.2420.3132.7620.2739.4318.55
Hour   10South20.9715.6818.7515.7718.2215.7718.4814.9317.2513.7516.5914.1318.5314.08
Hour   10TOTAL79.5241.2776.6140.5974.839.9875.1239.4968.8937.7964.838.1476.6136.38
Hour   11North17.914.6218.124.2218.043.8917.713.8616.133.7415.363.7418.653.75
Hour   11Central40.0520.9739.4120.5938.2620.3138.3520.735.0620.3132.6320.2739.4318.55
Hour   11South20.4515.6818.7915.7718.5415.7718.7914.9317.4113.7516.8614.1318.5314.08
Hour   11TOTAL78.4141.2776.3240.5974.8439.9874.8539.4968.6137.7964.8438.1476.6136.38
Hour   12North17.734.62184.2217.83.8917.343.8615.843.7415.263.7418.653.75
Hour   12Central39.5520.9739.0620.5938.1220.3138.0520.734.6320.2732.3420.2739.4318.55
Hour   12South19.9315.6818.815.7718.9315.7719.0414.9317.5213.7517.0314.1318.5314.08
Hour   12TOTAL77.2141.2775.8740.5974.8539.9874.4339.4967.9937.7664.6338.1476.6136.38
Hour   13North17.574.6217.874.2217.563.8917.023.8615.573.7415.163.7418.653.75
Hour   13Central39.0920.9738.7120.5937.9220.3137.6520.734.1120.2732.0420.2739.4318.55
Hour   13South19.4315.6818.8615.7719.3315.7719.2514.9317.5713.7517.1514.1318.5314.08
Hour   13TOTAL76.0941.2775.4440.5974.8139.9873.9339.4967.2537.7664.3538.1476.6136.38
Hour   14North17.474.6217.744.2217.373.8916.763.8615.293.7415.033.7418.653.75
Hour   14Central38.6120.9738.2820.5937.5520.3137.1620.733.5620.2731.720.2739.4318.55
Hour   14South19.0215.6819.0715.7719.7915.7719.4814.9317.713.7517.3114.1318.5314.08
Hour   14TOTAL75.141.2775.0840.5974.7239.9873.3939.4966.5437.7664.0338.1476.6136.38
Hour   15North17.324.6217.554.2217.163.8916.53.7915.093.7414.953.7418.653.75
Hour   15Central37.9720.9737.6920.5937.0820.3136.5820.733.0920.2731.4320.2739.4318.55
Hour   15South18.6515.6819.2615.7720.2415.7719.5514.9317.8413.7517.4314.1318.5314.08
Hour   15TOTAL73.9341.2774.540.5974.4839.9872.6339.4266.0237.7663.8138.1476.6136.38
Hour   16North17.224.6217.434.2216.983.8916.293.7915.033.7415.023.7418.653.75
Hour   16Central37.4320.9737.2320.5936.7520.3136.1220.732.8220.2731.4720.2739.4318.55
Hour   16South18.4115.6819.4115.7720.6115.7719.5914.9317.9813.7517.5914.1318.5314.08
Hour   16TOTAL73.0641.2774.0740.5974.3439.987239.4265.8337.7664.0738.1476.6136.38
Hour   17North17.324.6217.444.2217.053.8416.313.7615.223.7415.343.7418.653.75
Hour   17Central37.3120.9737.1120.5936.720.3135.9620.732.8520.2731.8520.2739.4318.55
Hour   17South18.4115.6819.5315.7720.8115.7719.5614.8518.1213.7517.7414.1318.5314.08
Hour   17TOTAL73.0441.2774.0740.5974.5739.9371.8339.3266.1937.7664.9338.1476.6136.38
Hour   18North17.554.6217.584.2217.33.8416.53.7615.623.7415.823.7418.653.75
Hour   18Central37.5520.9737.1920.5936.8320.3136.0319.933.1420.2732.420.2739.4318.55
Hour   18South18.5315.6819.5415.7720.7915.7719.4414.8518.1613.7517.8314.1318.5314.08
Hour   18TOTAL73.6241.2774.3140.5974.9239.9371.9738.5266.9137.7666.0538.1476.6136.38
Hour   19North17.764.6217.714.2217.573.8416.723.7615.983.7416.233.7418.653.75
Hour   19Central38.1720.9737.4220.5937.0720.3136.3219.933.6920.2733.1720.2739.4318.55
Hour   19South18.715.6819.4315.7720.615.7719.2614.3318.0613.7517.9114.1318.5314.08
Hour   19TOTAL74.6241.2774.5640.5975.2439.9372.33867.7237.7667.3238.1476.6136.38
Hour   20North17.914.6217.843.9417.863.8416.973.7616.263.7416.613.7418.653.75
Hour   20Central38.7921.0737.7720.5937.2920.3136.7819.934.4720.2734.0920.2739.4318.55
Hour   20South1915.6819.4515.7720.4915.7719.2114.3317.9513.7518.2514.1318.5314.08
Hour   20TOTAL75.741.3775.0740.375.6339.9372.963868.6837.7668.9638.1476.6136.38
Hour   21North17.754.6217.743.9417.843.8417.063.7616.363.7416.773.7418.653.75
Hour   21Central38.0921.0536.9620.5736.6320.2936.219.934.2120.2733.6820.2739.4318.55
Hour   21South19.1615.6819.5515.7720.5215.7719.1514.3318.0213.7518.4314.1318.5314.08
Hour   21TOTAL7541.3574.2540.287539.9172.413868.5937.7668.8838.1476.6136.38
Hour   22North17.244.6217.263.9417.433.8416.73.7616.033.7416.363.7418.653.75
Hour   22Central37.721.0535.4920.5735.2120.2934.9419.933.2420.2732.6220.2739.4318.55
Hour   22South18.815.6819.0815.7719.9115.7718.6914.3317.7213.7518.0114.1318.5314.08
Hour   22TOTAL73.7441.3571.8340.2872.5639.9170.343866.9937.7666.9938.1476.6136.38
Hour   23North16.574.5916.613.9416.853.8416.233.7615.583.7415.773.7418.653.75
Hour   23Central36.0121.0534.7420.5734.4520.2934.3819.933.0420.2731.4220.2739.4318.55
Hour   23South18.0715.6818.1515.7718.8915.7717.9514.3317.1313.7517.214.1318.5314.08
Hour   23TOTAL70.6541.3369.540.2870.1939.9168.563865.7537.7664.3938.1476.6136.38
Hour   24North15.974.5916.023.8416.273.8415.813.7615.143.7415.263.7418.653.75
Hour   24Central35.5721.0533.2620.5732.9520.0733.0419.931.9120.2731.4420.339.4318.55
Hour   24South17.2415.6817.1715.7717.8115.7717.0814.3316.414.1616.2614.1318.5314.08
Hour   24TOTAL68.7841.3366.4440.1967.0239.6965.923863.4538.1762.9738.1776.6136.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?

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Tom
Super User Tom
Super User

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:

Tom_0-1679427678023.png

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;

Tom_0-1679428290431.png

 

 

ballardw
Super User

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.

 

 

EyeNeedHelp
Calcite | Level 5

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.

ballardw
Super User

@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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1020 views
  • 0 likes
  • 4 in conversation