About robulon

robulon · ‎04-19-2021

Hi all, I've done quite a bit of searching online around this and can't find anything. I'm using Enterprise Guide version 7.15 on a Windows PC. I'm trying to create a flag variable in a proc sql program to identify whether a specific character variable contains the word 'unsold'. Here is my code: - proc sql; create table work.test as select * ,case when findw(stop_category_description,'unsold','','i') then 1 else 0 end as unsold from work.t1; quit; When I check the output dataset, there are no observations populated with 1 for the unsold variable. If however I view the output dataset in EG and click 'where' and paste the 'findw(stop_category_description,'unsold','','i')' code in, it identifies 31 observations, all of which I can see do contain the work 'unsold' in them but that are populated with a 0 for the unsold variable. I did see a previous post from a couple of years ago that someone had put about the findw function and the user was recommended to contact SAS Technical Services. I have a feeling this may also need their attention but thought I'd ask the community first in case there's something I'm missing. As always, any help will be gratefully received. Thanks, Rob

robulon · ‎05-07-2020

Hi, the range is &next_value._Rate, so is deleted in proc datasets, before being written afresh in the data step.

robulon · ‎05-07-2020

Thanks Reeza, I'll have a look into this. I'm not sure that what you're saying about the size of the data is quit correct for a couple of reasons. Firstly, I use this approach in a number of other programs which add a new observation each day so the size of the data set increases by one each time and they all work. The other reason being that my understanding is that by deleting the range using proc datasets, the initial size of it shouldn't come into play. I will have a look at running the program in its individual parts and see if that tells me anything - thanks.

robulon · ‎05-06-2020

Hi, I've been using the libname Excel method of writing to a named range in an Excel sheet for a few monbths, since a colleague at work introduced me to the technique and have found it very useful. I am having issues with it at the moment though in as much as for a particular sheet, when I run the code, the log tells me the code has run successfully but the Excel sheet has not been updated. I have run the code a couple of times today and it has worked (the Excel file shows as having been modified at 11:08 today) but when I have run it again since, it is not updating. I have checked that it was trying to write to the correct location (which I knew it was) by running the code with the Excel file open and the code errors, and I have checked that the named ranges still exist within the file (which they do) so I just don't know what else it could be. The output datasets have three variables in, a date, a character variable and a number (in percent format) - example attached. The code I am using is below: - %let xlpath= ##\Covid\Arrears_Summary_Covid_&covid..xlsx; libname xl Excel "&xlpath."; proc datasets lib=xl nolist; delete &next_value._Rate; quit; data xl.&next_value._Rate; set &next_value._totals_2_covid_&covid.; run; libname xl clear; And the log shows this: - NOTE: Libref XL was successfully assigned as follows: Engine: EXCEL Physical Name: ##\Covid\Arrears_Summary_Covid_1.xlsx NOTE: Deleting XL.Improve_Rate (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds 11 The SAS System 10:39 Wednesday, May 6, 2020 cpu time 0.00 seconds NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. NOTE: There were 52 observations read from the data set WORK.IMPROVE_TOTALS_2_COVID_1. NOTE: The data set XL.Improve_Rate has 52 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Libref XL has been deassigned. Does anyone have any thoughts as to what could be causing this, only it's a huge frustration for me at the moment. Nothing of any consequence has been changed in the code between runnings so I'm really at a loss as to why it worked previously but not now. Thanks, Rob

robulon · ‎03-25-2020

Hi Kurt, I am writing to a named range in the Excel sheet. When I opened the Excel sheet the data in the cell just showed as 16:34:15, however if I typed in the 40:34:15 and applied the [h]:mm:ss format, it showed the correct figure, so the issue seemed to be in how Excel was interpreting the data I was trying to write into it.

robulon · ‎03-25-2020

Not sure if it's forum etiquette to mark your own solution but there you go

robulon · ‎03-25-2020

I've managed to sort this, I've used put to create the time variables as character and they are exporting into the Excel sheet as required: - put(round(sum(talk),1),time.) as total_time

robulon · ‎03-25-2020

Hi, I'm a bit stuck here and haven't been able to track a solution down so hoping for a bit of assistance. I have written some sas code that uses proc sql to sum up telephone call times and produces a total number of hours, minutes and seconds that a team has completed each day. I have applied the time. format so get, for example, for one day I get a total of 40:34:15 which is great. The problem comes when I look to export the report to Excel for distribution. Excel seems to be automatically applying a time of day format, so for the above example, it is displaying 16:34:15. Can anyone suggest a way around this? Ideally I just want to be able to run the code and be in a position to send out the excel report. Many thanks, Rob

robulon · ‎03-16-2020

If you need the values of schemenumber from both tables, you would need to do a full join and use the coalesce function (coalesce(a.schemenumber,b.schemenumber) as schemenumber). This would then merge on and schemenumbers that appear in both tables, but would also include any that appear in the second table but not the first.

robulon · ‎03-16-2020

Hi, The reason this is happening is because when you select *, you are telling sas you want to include everything from both the tables, and as SchemeNumber exists on both tables you can't have two variables with the same name. In order to get rid of it, you would need to tell SAS which of the two tables you want to pull SchemeNumber from.

robulon · ‎03-13-2020

Thanks Reeza, It's no biggie as I've already got something in place that does what I need to do (I'm just proc exporting to a different tab, then using formulas to pull the data in to where I was hoping I could write the named range to). I was just interested as to whether I was missing something fundamental. Cheers, Rob

robulon · ‎03-12-2020

Hi all, I'm a bit puzzled and hoping someone can shed some light on what's causing this issue. I was recently introduced by a colleague at work to the idea of writing data to an Excel sheet using a named range (already existing on the Excel book, but deleted using proc datasets then written again in a data step). I have done this successfully in EG as follows: - %let xlpath=########/Arrears Level Charts.xlsx; libname xl Excel "&xlpath"; proc datasets lib=xl nolist; delete arrs_level.; quit; data xl.arrs_level.; set arrs_level._trans_2; run; libname xl clear; I have tried to use the same concept in SAS Studio but rather than the data being written to the named range on an existing tab, it is creating a new tab in the workbook with the named range name as the tab name, and creating the named range in that sheet. The only difference in the coding is that I have had to use the xlsx engine rather than the Excel one in my libname statement. Is anyone able to suggest why this might be happening, and what I would need to do to get the data written to the existing named range? Thanks in advance for any suggestions. Rob

robulon · ‎12-06-2019

Oh dear, how embarrassing Can't even try the excuse that it was a slip of the fingers as 2 and 7 are nowhere near each other! The loosening of the stopping rule seems to have worked. I've created a macro variable of the target amount earlier in the code using sql to calculate the difference between the expected amount and the actual amount and have use the code if sum > &outstanding. + 9 then leave; Interestingly enough (for me anyway), if I added a value that was any less than 9 to the macro variable, no results were returned whereas if I use 9 or higher, it correctly identifies the amounts. Huge thanks for the help people, always impressed by the number of members who are happy to share their expertise and help us less knowledgeable types develop their skills. next step for me is to work my way through the code so that I understand how it's doing what it's doing. Thanks again, Rob

robulon · ‎12-05-2019

Thank you @s_lassen and @FreelanceReinhard, and apologies for not expressing my thanks earlier, I got a bit caught up in other matters. I've tried to resolve my issue using this solution but it appears to be falling down somewhere. Although I now know the figures that make up the total (obtained through other means but would still be great for future reference to get this sorted) are 75.9 and 106.97, the code does not identify this particular combination. In fact, it does not identify any combinations at all. I have tried amending the code using round(,0.1) and this does return some combinations but not the exact one. I've tried some more testing putting the figures in as datalines to reduce the number of figures but it still isn't working. Would you be able to suggest where I'm going wrong? I'm wondering if it is something to do with the decimal places but have tried multiplying all the figures by 100 to turn them all into integers but am still not arriving at the solution. This is what I'm using data amounts; input amount; datalines; 75.9 106.97 100 255 643.59 251.25 ; run; %let outstanding = 187.87; 　 data want; array payments (1000) 8 _temporary_; do _N_= 1 to nobs; set amounts nobs = nobs; payments(_N_) = amount; end; array idx 8 idx1 - idx6; %macro check; %local comb; %do comb = 2 %to 6; call missing(of idx1 - idx&comb.); ncomb = comb(nobs, &comb.); do i = 1 to ncomb; rc = lexcombi(nobs, &comb., of idx1 - idx&comb.); sum = 0; do _N_ = 1 to &comb.; sum + payments(idx(_N_)); end; if round(sum,0.01) > &outstanding. then leave; if round(sum,0.01) = &outstanding. then output; end; %end; %mend check; %check; keep idx1 - idx6; run; As always, any advice will be gratefully received. Rob

robulon · ‎11-26-2019

Hi, I'm hoping for some suggestions on how to go about solving a problem. I have a total of 253 payments due that should add up to £14,843.87. The total payment received is £14,661.00 but I am unable to tell how many individual payments make up this amount. What I am hoping to do is run some code that will identify the possible combinations of the due payments that add up to the missing £182.87. I know the missing amount must be made up of more than one payment (as the biggest single payment is £115.40), and cannot be made up of more than six payments (as the lowest seven payments add up to £189.89). Is there a way I can run some code that will look at the different possible combinations of the payment amounts and tell me which one(s) add up to £182.87. As always, really grateful for any advice on how to go about solving this. Thanks, Rob

Online Status	Offline
Date Last Visited	‎02-10-2022 03:19 PM

Re: Help with using two arrays please

Re: Help with using two arrays please

Re: Help with using two arrays please

Re: Help with using two arrays please

Help with using two arrays please

Re: Writing Excel formulas in SAS using ods

Re: Writing Excel formulas in SAS using ods

Re: Writing Excel formulas in SAS using ods

Writing Excel formulas in SAS using ods

Re: findw function not working correctly n proc sql

Re: Help with using two arrays please

Re: Help with using two arrays please

Re: Help with using two arrays please

Re: findw function not working correctly n proc sql

Re: findw function not working correctly n proc sql

Re: Help with using two arrays please

Re: I have to join two table but it is giving an error

Re: I have to join two table but it is giving an error

Re: Inconsistencies using proc export (the dreaded //)

findw function not working correctly n proc sql

Re: Writing to Excel named range - code runs successfully but Excel sh...

Re: Writing to Excel named range - code runs successfully but Excel sh...

Writing to Excel named range - code runs successfully but Excel sheet ...

Re: Export to Excel - Number of hours rather than time of day

Re: Export to Excel - Number of hours rather than time of day

Re: Export to Excel - Number of hours rather than time of day

Export to Excel - Number of hours rather than time of day

Re: I have to join two table but it is giving an error

Re: I have to join two table but it is giving an error

Re: SAS Studio - Writing to a named range in Excel

SAS Studio - Writing to a named range in Excel

Re: Identifying combinations of variable that add up to a set amount

Re: Identifying combinations of variable that add up to a set amount

Identifying combinations of variable that add up to a set amount