cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
robulon
‎02-10-2022
  • 82 Posts
  • 16 Likes Given
  • 3 Solutions
  • 5 Likes Received

Re: Help with using two arrays please

by Quartz | Level 8 in SAS Programming
‎02-10-2022 04:06 AM
‎02-10-2022 04:06 AM
Thanks for this, a really interesting solution, and not something I've seen done before in terms of explicitly making two passes through the data - you've definitely given me something to investigate further. Thanks again ... View more

Re: Help with using two arrays please

by Quartz | Level 8 in SAS Programming
‎02-10-2022 04:05 AM
‎02-10-2022 04:05 AM
Brilliant, thank you ... View more

Re: Help with using two arrays please

by Quartz | Level 8 in SAS Programming
‎02-10-2022 04:05 AM
1 Like
‎02-10-2022 04:05 AM
1 Like
Thank you, this is great. For some reason, I had in my head I needed to have the values through each interim year which is what my code produces but the reality is I only need to know the impact now and this does exactly what I need. Thanks again ... View more

Re: Help with using two arrays please

by Quartz | Level 8 in SAS Programming
‎02-09-2022 11:23 AM
‎02-09-2022 11:23 AM
Sorry, the reason for the title mentioning arrays is because I thought arrays might be the way forward but wasn't sure how I could get it to cycle through the different options when one would be going from 1 to 5 and the other would be going from 2016 - 2020. The other reason I thought arrays would work better is because I thought I could future proof it a bit more and not have to add lines of code for each new years worth of data. Thanks again ... View more

Help with using two arrays please

by Quartz | Level 8 in SAS Programming
‎02-09-2022 11:22 AM
‎02-09-2022 11:22 AM
Hi, I feel like I should know how to do this but am struggling to get my head around it. I have created a dataset that contains the cpi for the last six years (from 2016 - 2021). What I want to do is for each year, multiply the starting point by the figure for the subsequent years and ending up with what the total cpi for each starting year would be in 2022. So for 2016, I would want 2016 * 2017 * 2018 * 2019 * 2020 * 2021, whist for 2020, I would only want 2020 * 2021.   I have been able to write code that does this but I just feel like there has to be a cleverer way of doing it, which is why I'm asking all you clever people!   This is my existing code: -   data annual_cpi_01; input year annual_cpi; datalines; 2016 1.007 2017 1.027 2018 1.025 2019 1.018 2020 1.009 2021 1.026 ; run; data annual_cpi_02; set annual_cpi_01; retain _2016 _2017 _2018 _2019 _2020; if _n_ = 1 then _2016 = annual_cpi; else _2016 = _2016 * annual_cpi; if _n_ = 2 then _2017 = annual_cpi; else _2017 = _2017 * annual_cpi; if _n_ = 3 then _2018 = annual_cpi; else _2018 = _2018 * annual_cpi; if _n_ = 4 then _2019 = annual_cpi; else _2019 = _2019 * annual_cpi; if _n_ = 5 then _2020 = annual_cpi; else _2020 = _2020 * annual_cpi; run;   If anyone could suggest an alternative, I'd really appreciate it.   Many thanks, Rob ... View more

Re: Writing Excel formulas in SAS using ods

by Quartz | Level 8 in SAS Programming
‎05-15-2021 03:25 AM
‎05-15-2021 03:25 AM
It's for a betting bot that I use. Basically, I have a SAS program that creates an Excel sheet with all the horses that meet the criteria to make them worthy of consideration for a bet. I then run the bot which identifies the odds available for each of the possible selections but will only place a bet if the odds available represent value for that particular selection.   The sheet that I am writing the formulas to uses lookups to pull data on the horses that have been backed so that information is not available to SAS at the time I run my scripts so my code dynamically creates the formulas based on the cell references that are required for the lookup.   I have tried experimenting with the excelxp.tagsets ods but am now experiencing further issues whereby one of the formulas (the lengthy one) is throwing out errors when I open the xml and the log states it is seeing it as a 'Bad Value' but I haven't been able to work out why, and certainly the formulas all work fine when I use ods html to create an xls file, I just thought it would be nice to not have the message about the file type mismatch every time I open the sheet.   Anyway, it's no biggie, I'll just carry on with what I have. As with a lot of these things, I was hoping someone had come across something similar in the past and would have a quick fix but that doesn't appear to be the case.   Thanks anyway ... View more

Re: Writing Excel formulas in SAS using ods

by Quartz | Level 8 in SAS Programming
‎05-14-2021 06:51 AM
‎05-14-2021 06:51 AM
Yes, I did thanks and I did look at both of these papers, however due to the fact that they were both over ten years old, I felt it was quite likely that there would be a more recent, and possibly more efficient solution. Thanks for the taking the time to provide the links though. ... View more

Re: Writing Excel formulas in SAS using ods

by Quartz | Level 8 in SAS Programming
‎05-13-2021 04:31 AM
‎05-13-2021 04:31 AM
Thanks for this. I should have specified in my original post, I'm using SAS Studio so DDE is not an option unfortunately. ... View more

Writing Excel formulas in SAS using ods

by Quartz | Level 8 in SAS Programming
‎05-12-2021 04:00 AM
‎05-12-2021 04:00 AM
Hi all,   I'm hoping someone can assist me with this - I have written some code that creates a data set where a couple of the variables are Excel formulas. What I want to be able to do is then to export the data set as an Excel file and for the formulas to be active as soon as I open the sheet.   This is the part of the code that creates the Excel formula variables (the variables in question are Exchange1 and backed): -   data all_sel_4_1; set datasets.all_sel_2_1_&csv_file.; length Exchange1 $ 300; horse = compress(horse,"`"); cell_1 = strip(put(_n_ + 1,best.)); cell_2 = strip(put(_n_ + 4,best.)); Exchange1 = cats("=IF(ISNA(VLOOKUP(D",cell_1,",&excel.,3,FALSE)),",'"','"',",VLOOKUP(D",cell_1,",&excel.,3,FALSE))"); backed = cats('=IF(F',cell_1,'="",0,1)'); time_run = timestamp; drop cell_1 cell_2 timestamp; run;   I have little experience in using ods but if I try to create an xlsx file using this code  ods excel file="/folders/myfolders/Excel/Outputs/&csv_file._Merge_2.xlsx" style=normal options(formulas='on'); proc print data=all_sel_4_1 noobs; run; ods excel close;   the xlsx file is created but when I open it, the Exchange1 variable shows the full text of the formula in the cell. If I click the cell, press F2 and enter, that activates the formula (forgive me if that isn't the correct terminology). The formula for the backed variable is active as soon as I open the sheet.   To get around this, I have been using ods html and creating an xls file: -   ods html file="/folders/myfolders/Excel/Outputs/&csv_file._Merge_2.xls" style=normal options(formulas='on'); proc print data=all_sel_4_1 noobs; run; ods html close;   This does work but when I open the file, it gives me a warning about the file format and extension not matching. I would like to be able to use ods Excel but am just not sure what I need to do differently in order to ensure that all the formulas are recognised as such when the file is opened. The only difference I can see in how the two formulas are created is around the quote marks I have used, but in the Exchange1 variable, I need to use the double quotes in order to resolve the macro variable.   As always, any ideas or suggestions will be gratefully received. Thanks, Rob ... View more

Re: findw function not working correctly n proc sql

by Quartz | Level 8 in SAS Programming
‎04-19-2021 11:22 AM
‎04-19-2021 11:22 AM
Legend! Thanks Tom ... View more

Re: findw function not working correctly n proc sql

by Quartz | Level 8 in SAS Programming
‎04-19-2021 10:39 AM
‎04-19-2021 10:39 AM
Having said that, I'm still a bit confused as to why I don't need a space when doing it in a data step but I do in proc sql but I'll worry about that another time! ... View more

Re: findw function not working correctly n proc sql

by Quartz | Level 8 in SAS Programming
‎04-19-2021 10:38 AM
‎04-19-2021 10:38 AM
That's brilliant, thank you.   I was only including the third parameter as my understanding is if you are applying a modifier, you also have to apply a delimiter parameter so that SAS knows you are intending to use the 'i' as a modifier and not a delimiter, however I hadn't appreciated that I would also need to include a space.   I should have known it was me doing the wrong thing (again) but that's been really helpful.   Thanks ballardw and thanks to everyone for looking.   Rob ... View more

Re: findw function not working correctly n proc sql

by Quartz | Level 8 in SAS Programming
‎04-19-2021 10:29 AM
‎04-19-2021 10:29 AM
Thanks for the suggestions. I've not been able to download the data2datastep zip file as my work pc will not allow it but have looked to recreate the issue using some datalines code (hope that's ok): -   data l1; input stop_category_description $30.; datalines; Covid Customer Care Post Covid Arrangement Agreed Covid Customer Care New VT Unsold Covid Customer Care Covid Customer Care Post Covid Covid Customer Care Post Covid Arrangement Agreed Post Covid Covid Customer Care Covid Customer Care New Repossession Unsold New Repossession Unsold New VT Unsold Post Covid Arrangement Agreed Covid Customer Care Post Covid Covid Customer Care Covid Customer Care Covid Customer Care Covid Customer Care Covid Customer Care Covid Customer Care Covid Customer Care Covid Customer Care Post Covid Arrangement Agreed New VT Unsold Post Covid Arrangement Agreed New IVA ; run; proc sql; create table l2 as select stop_category_description ,case when findw(stop_category_description,'unsold','','i') then 1 else 0 end as unsold from l1; quit; data l3; set l1; if findw(stop_category_description,'unsold','','i') then unsold = 1; else unsold = 0; run;   when I run these three programs, the proc sql one does not populate the 'unsold' variable with any 1 values whereas when I do the same in the data step, it correctly identifies them.   As I say, I can work around it anyway but I like to understand why things don't work as for the most part, it's because I'm doing something incorrectly and want to try to learn the right way of doing things but in this case, I'm not sure that is what's happening.   Thanks again ... View more

Re: findw function not working correctly n proc sql

by Quartz | Level 8 in SAS Programming
‎04-19-2021 10:14 AM
‎04-19-2021 10:14 AM
Just to add, I've also coded this up in a data step: -   data work.t3; set work.t1; unsold = 0; if findw(stop_category_description,'unsold','','i') then unsold = 1; run;   and this has worked perfectly so it seems to be something to do with how proc sql is implementing the function. ... View more

Re: findw function not working correctly n proc sql

by Quartz | Level 8 in SAS Programming
‎04-19-2021 10:07 AM
‎04-19-2021 10:07 AM
I'm going to struggle to do that I'm afraid as the dataset contains sensitive customer data (apologies, I had meant to include that information in my original post).   I had hoped that I had provided enough information to demonstrate that the function worked within the where facility in the EG viewer but not in the code itself. I've also just tried doing the same in the source dataset (work.t1) and it has correctly identified the correct observation there as well.   Not to worry, I can easily work around it for the time being anyway, I just thought it might be something that someone had come across before. If I can think of a way to provide the data in the required format, I will do.   Thanks anyway  ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎02-10-2022 03:19 PM
Likes given to
Likes from
Quick links