About jo1

jo1 · ‎02-06-2022

Yes, for context, day original a date, basically I have 7 days of data, each with values for 48 x 30 min periods (this will be scaled up to 6mths or 1 year of data). Correct I have repeated it 7 times. Basically this is electricity load and we are trying to optimise the addition of Control Load - so I want to do the same calculation on all days (which could be up to 365) the max for each period across the data set will optmise the setting for that dataset. So AIM is the value at which the sum of the max's equals 1 or in which we have utilised 100% of the Load available. Yep, I see how it could be done a lot simplier - so I have now this - just 3 simple steps. %Let Aim = 1932; /*Calculates the value for each day/period*/ Data Test_data set AIM_ANALYSIS; Per = ifn(Load > &AIM,0,(&AIM - Load)/Total_CL); run; /*Calculates the max percentage setting for each period*/ Proc sql; create table Test_Output as select Period ,max(Per) as Max_value from Test_data group by Period; run; /*Sums the day*/ Proc sql; create table Test_Total as select sum(Max_value) from Test_Output; run; So I still don't have my answer to the question - what value of AIM results in the final calculation (Test_Total) equaling 1. So just looking into how to do that piece.

jo1 · ‎02-06-2022

Ok, in looking at the comments so far, I think I was making this much harder than it should be, It's just a table calculation and then a summary table to get the max. In explanation I actually need to solve it for what value of "Aim" is required to have the max values for the period = 1 (or 100%). So I think that is really the challenge and I was making the first bit harder because I had my eye on that.

jo1 · ‎02-06-2022

Probably Tom, so here is my actual code, but harder to give you the data set to make it work. So this code works, as it calculates the value for each of the days for the time period in my table "AIM_ANALYSIS" - it has all the values pertaining to each day and time period of the sample (Day; Period; Load; Total_CL). What I want is the max value (of this calculation) for each time period (across in this example the 7 days). This code results in the table CALC_AIM which is exactly that (Period; Days (this is just the total days in the dataset); Per) What I would like, is to not have this in long hand and have an equation for each day.... not sure that I'm explaining this any clearer. %Let Aim = 1932; Proc sql; Create table CALC_AIM as Select Period ,Count(Day) as Days FROM LOAD_PERIOD Group by Period; Alter table CALC_AIM Add Per num; Update CALC_AIM set Per = max( ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 1)), ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 2)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 2))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 2)), ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 3)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 3))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 3)), ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 4)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 4))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 4)), ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 5)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 5))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 5)), ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 6)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 6))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 6)), ifn((select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 7)>&AIM,0,(&AIM - (select Load from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 7))/(select Total_CL from AIM_ANALYSIS as a where a.Period=CALC_AIM.Period and a.Day = 7))); run;

jo1 · ‎02-06-2022

Hi, I've overly simplify what I am trying to do so that I might be able to get some assistance on how to structure it and then I can add the complexity back in. I need to find the max of a set of calculations that uses values for each day (which I have in another table) - and the number of days I have could be different depending on the data so I want a set of code that can incorporate various numbers of days. So as a simple example this is what I want it to do: Proc sql; data test; attrib y length=3 format=best3.; run; Proc sql; update test set y = max(1*100,2*100,3*100); run; But without having to put the 1, 2, 3 in the calculation so that it would just size up to how many days I have - I was thinking that I would calculate how many days of data I had and then put that in a do loop - I'm still simplifying this as I'm just saying it's 3 days worth (will add the variable of total days later) - this is what I was thinking would work, but it is not. data test; attrib y length=3 format=best3.; run; %Macro Fmax; Proc sql; update test %do i=1 %to 3; set y = max(i*100) %end; %mend Fmax; %Fmax; Any help would be appreciated.

jo1 · ‎12-06-2020

It was just a view issue, I closed down the project view and reopened it and they were all back.

jo1 · ‎12-06-2020

Has anyone lost Process Flows? I'm working in version SAS Enterprise Guide 8.1 and had about 6 or so process flows within the file on Friday. I opened the file today and only the original one exists. There was a recovered version which I also tried but that also only had the one. Anyone had this happen or has found a way to get them back? Thanks

jo1 · ‎12-20-2017

Hi, I have been given a txt file that has multiple tables of data in it- each is a matrix of the connectivity of a network. I want to import each table, sum the number of segments each is attached to (to identify which segments are at the top of the network relative to the others) and then append them into a single table. The number of columns and rows changes in each of the tables (as different parts of the network have varying numbers of segments). I've done it for a single file (example below): ,DBS3B1,30-R12884,30-R68054,30-R1542,30-R1585,30-R1587,30-R1540,30-R1541,30-R188,30-R336,30-R68935,30-R2882,30-R2570,30-R3197,30-R3008,30-R13213 DBS3B1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 30-R12884,,1,,,,,,,,,,,,,, 30-R68054,,,1,1,,,,,,,,,,,, 30-R1542,,,,1,,,,,,,,,,,, 30-R1585,,,,,1,1,1,1,,,,,,,, 30-R1587,,,,,,1,1,1,,,,,,,, 30-R1540,,,,,,,1,,,,,,,,, 30-R1541,,,,,,,,1,,,,,,,, 30-R188,,,,,,,,,1,1,1,1,1,1,1,1 30-R336,,,,,,,,,,1,1,,,,, 30-R68935,,,,,,,,,,,1,,,,, 30-R2882,,,,,,,,,,,,1,1,1,1, 30-R2570,,,,,,,,,,,,,1,,, 30-R3197,,,,,,,,,,,,,,1,, 30-R3008,,,,,,,,,,,,,,,1, 30-R13213,,,,,,,,,,,,,,,,1 Part of the OUTPUT: DBS3B1 16 30-R12884 1 30-R13213 1 30-R1540 1 30-R1541 1 30-R1542 1 30-R1585 4 30-R1587 3 30-R188 8 The issue that I'm having is that they have given me a single file with a matrix for each feeder of the network - here is an example of the format showing 2 feeders (tables) - the first row of the new table is ,CBP142 33kV,30-R3893,30-F3890 - can someone given me a start on how to get SAS to read this and identify that there are 2 tables, my actual file has hundreds of matrixs so I can't do it manually. ,CBP142 22kV,30-R11970,30-R12880,30-FL62656,30-F3945 CBP142 22kV,1,1,1,1,1 30-R11970,,1,1,1, 30-R12880,,,1,, 30-FL62656,,,,1, 30-F3945,,,,,1 ,CBP142 33kV,30-R3893,30-F3890 CBP142 33kV,1,1,1 30-R3893,,1,1 30-F3890,,,1 I'm using SAS Enterprise Guide 7.1

jo1 · ‎06-22-2017

Hi, I have a large amount of customer data that is a value for each half hour period of the day. I have the data table set up with Date; Time; and then each column is a new customer. In case it helps what I'm doing is looking at a combination of the customers eg 2 at time, 5, 10, 100 etc to work out at what level the diversity of the single customer is lost - ie as the group size increases my time of max demand will stablise. I have come up with a way to sample the customers for a particular level (eg this code sums the values of 10 random customers for each time and date period) but I'm sure there must be a shorter way to code this for say when I want to do a level of 100. So basically I'm looking for a shorter way to create the sequence of random numbers as variables to eliminate the length of the first data step and then also a shorter way to write the R1= in the second datastep. /* SAS macro that duplicates teh Excel RANDBETWEEN function*/ %macro RandBetween(min, max); (&min + floor((1+&max-&min)*rand("uniform"))) %mend; /*Randomly select the columns for the calculation */ data _null_; set random; call symput("rand1",%RandBetween(1,4747)); call symput("rand2",%RandBetween(1,4747)); call symput("rand3",%RandBetween(1,4747)); call symput("rand4",%RandBetween(1,4747)); call symput("rand5",%RandBetween(1,4747)); call symput("rand6",%RandBetween(1,4747)); call symput("rand7",%RandBetween(1,4747)); call symput("rand8",%RandBetween(1,4747)); call symput("rand9",%RandBetween(1,4747)); call symput("rand10",%RandBetween(1,4747)); run; /*Sums the 10 random columns*/ data Sample; set Cust_T; array Col[4747] col1-col4747; /* # columns = # customers */ R1=Sum(Col(&Rand1)+Col(&Rand2)+Col(&Rand3)+Col(&Rand4)+Col(&Rand5) +Col(&Rand6)+Col(&Rand7)+Col(&Rand8)+Col(&Rand9)+Col(&Rand10)); DROP col1-col4747 n _Name_; run;

jo1 · ‎06-19-2017

I have been able to adjust this code to use when I have larger values of n but this gets out of hand quickly. Can you give a clue on how to alter this code to only look at combinations of a set number of values. Eg I might have 5 columns but I don't want to look at all possible 31 combinations but only the combinations that have 3 sample points in each combination = 10 combinations. Thanks

jo1 · ‎06-08-2017

"Help" being the important word here lol - yep I have a call in and we have made a once off change for the moment. I do want to be able to test the different values etc and was hoping to reduce the wait times. Getting admin rights (although this is near to impossible) and/or logging a call each time I want/need to make a change are both solutions but I was trying to see if I could bypass that process. Thanks

jo1 · ‎06-08-2017

Sorry should have said. It's just a single licence running on a local machine. Thanks

jo1 · ‎06-08-2017

Hi, I am using SAS Enterprise Guide 7.1 and would like to change the MEMSIZE and have found ways to do this but have been coming up with the following issues. 1. I do not have admin rights on my computer so cannot change the config file where it currently is placed. 2. I cannot work out how to change the location of the config file on start up so that I can save the config file in a location where I do have rights to change it. 3. I was able to put -MEMSIZE 0 after the exe file in the Target of the start up icon for SAS 9.4 but this doesn't work for EG, TARGET: "C:\Program Files\SASHome\SASEnterpriseGuide\7.1\SEGuide.exe" -MEMSIZE 0 4. Although some of the things in the config file can be change in code it doesn't appear that MEMSIZE is one that you can If anyone has a way around this issues it would be greatly appreciated. Jo

jo1 · ‎06-08-2017

Hi, I am using SAS Enterprise Guide 7.1 and want to change the MEMSIZE. I've found a few ways to do this but am coming up with problems to them - I'm hoping someone else has gotten around the issues I have: 1. I do not have admin rights over my computer so can not change the config file 2. I can't work out how to get SAS on start up to point to a different location for the config file (that way I could save it in a location where I can make changes). 3. I have tried putting -MEMSIZE 0 in the Target after the exe file on the start up icon, this appears to work for SAS 9.4 but doesn't seem to work the same way for EG 4. It seems that although I can change some things in the config file by code the MEMSIZE is not one of them Any help would be appreciated.

jo1 · ‎05-30-2017

Thanks yes, after a bit I worked out to increase the dimensions of the arrays and adjusted the width of the BINARY. I did try adding s to the KEEP statement but I'm assuming it would have been just the last combination that it was showing. But continuing that line of thought I created a new table with the number of combinations and their BINARYw number (only the 1's) data new; do i = 1 to 29; s = compress(put(i,Binary5.),"0"); output; I needed to transpose the data anyway so the Combinations (columns) become a variable and then I was able to join the two tables together so instead of having Col1 - Col29 I returned the binary tag created in the new table 1, 11, 111 etc which identified them. It is a bit of mucking around but I couldn't think of another way to do it. I'm not sure if I should have been able to label the column headings with the new table results somehow.

jo1 · ‎05-30-2017

I know this post was some time ago, but I found in useful in working out how to do a similar thing but with more variables. The thing I'd like to be able to do but I can't think how is to know which of the combinations were 1, 2, 3 etc factor combinations - any suggestions of where to start looking for an answer. Jo

Online Status	Offline
Date Last Visited	‎03-09-2022 02:28 AM

Re: Using do loop in calculation

Re: Using do loop in calculation

Re: Using do loop in calculation

Using do loop in calculation

Re: Lost Process Flows

Lost Process Flows

Reading txt file with multiple tables

Multiple call symput

Re: neat way to sum all Combinations

Re: MEMSIZE no admin rights

Re: Using do loop in calculation

Re: Using do loop in calculation

Re: MEMSIZE no admin rights

Re: Substitute arithemtic for value in table

Re: Using do loop in calculation

Re: Using do loop in calculation

Re: Using do loop in calculation

Using do loop in calculation

Re: Lost Process Flows

Lost Process Flows

Reading txt file with multiple tables

Multiple call symput

Re: neat way to sum all Combinations

Re: MEMSIZE no admin rights

Re: MEMSIZE no admin rights

MEMSIZE no admin rights

MEMSIZE no admin rights

Re: neat way to sum all Combinations

Re: neat way to sum all Combinations