12-17-2021
FredGIII
Quartz | Level 8
Member since
06-23-2011
- 58 Posts
- 7 Likes Given
- 0 Solutions
- 23 Likes Received
-
Latest posts by FredGIII
Subject Views Posted 1294 09-24-2019 01:41 PM 1340 09-24-2019 11:37 AM 1348 09-24-2019 11:06 AM 1604 09-18-2019 08:02 AM 2141 06-25-2019 11:27 AM 2166 06-25-2019 11:02 AM 5435 12-06-2018 12:45 PM 7650 10-21-2014 11:27 AM 7650 10-21-2014 10:30 AM 8130 10-21-2014 10:16 AM -
Activity Feed for FredGIII
- Got a Like for VA - Slider Controls object option. 09-07-2021 08:47 AM
- Got a Like for Overlay histograms - all data vs subset. 03-25-2020 08:36 PM
- Liked Re: ODS Graphics Designer Code - results not the same run in SAS Studio for DanH_sas. 09-24-2019 01:42 PM
- Posted Re: ODS Graphics Designer Code - results not the same run in SAS Studio on Graphics Programming. 09-24-2019 01:41 PM
- Posted Re: ODS Graphics Designer Code - results not the same run in SAS Studio on Graphics Programming. 09-24-2019 11:37 AM
- Posted ODS Graphics Designer Code - results not the same run in SAS Studio on Graphics Programming. 09-24-2019 11:06 AM
- Liked Re: Where are SAS Studio Snippets stored? for AnandVyas. 09-18-2019 10:02 AM
- Posted Where are SAS Studio Snippets stored? on SAS Studio. 09-18-2019 08:02 AM
- Tagged Where are SAS Studio Snippets stored? on SAS Studio. 09-18-2019 08:02 AM
- Tagged Where are SAS Studio Snippets stored? on SAS Studio. 09-18-2019 08:02 AM
- Posted Re: SGPLOT only using half the graph space (offsetmax default value) on Graphics Programming. 06-25-2019 11:27 AM
- Liked Re: SGPLOT only using half the graph space (offsetmax default value) for Rick_SAS. 06-25-2019 11:23 AM
- Posted SGPLOT only using half the graph space (offsetmax default value) on Graphics Programming. 06-25-2019 11:02 AM
- Posted Re: Run SAS code remotely on a Linux server on SAS Programming. 12-06-2018 12:45 PM
- Liked Re: VA - Date as parameter - Status changed to: Implemented for AnnaBrown. 05-22-2018 01:21 PM
- Got a Like for VA - Date as parameter. 08-31-2017 07:08 PM
- Got a Like for VA - Date as parameter. 03-20-2017 04:04 PM
- Got a Like for VA - Date as parameter. 03-01-2017 12:00 PM
- Got a Like for VA - Slider Object - set default values on open. 02-23-2017 10:32 AM
- Got a Like for VA - Date as parameter. 02-20-2017 06:48 AM
-
Posts I Liked
Subject Likes Author Latest Post 1 1 2 1 1 -
My Liked Posts
Subject Likes Posted 2 02-12-2016 08:18 AM 11 02-11-2016 08:18 AM 5 02-11-2016 07:59 AM 1 05-20-2013 09:31 AM 2 10-21-2014 10:16 AM
07-25-2014
09:32 AM
Hi everyone, I have created a stored process to pull a data set from a database that I want to stream to Excel. I want the user to be able to launch the STP from Excel and auto populate the data into an Excel tab. When I run the program on EG the resulting data set is approx 11MB exported to a CSV file. But when I create a report, the size jumps to over 120MB!!!! My understanding is that I have to create the report in order to stream it to Excel. Is that correct? Is there a more efficient means to do this so that I can stream it to Excel? There are 127K rows which is well within the limits for Excel, but the report size is over 10x the csv file size. Any suggestions? Thanks, Fred
... View more
07-21-2014
05:40 PM
Hi everyone, Anyone seen this type of behavior? : SAS EG 5.1 I have created a fairly simple stored process that has 3 prompts (location, fault type, date range). Originally I had all three in the same query builder. Both the location and fault type were dynamic list prompts and have a lot of choices (968 for Location, 560 for fault type). I allow the user to select up to 10 locations and 5 fault types to filter against and a date range. Everything works great when I run the STP in EG, but when I try to run it from Excel, I get a generic message: SAS Add-In for Microsoft Office has encountered a problem We recommend that you save your work and restart SAS Add-in for MS Office. Under Details: Error creating window handle. When I eliminated the prompt for fault type and hard coded that in, the problem went away and it worked fine from Excel. Is anyone aware of prompt limitations within the MS Add-in framework? Is there a work around? Thanks, Fred
... View more
07-01-2014
07:31 AM
Hi everyone, I am trying to create a block or riser map that shows the total cost of each site shown by location. The riser block height is determined by the cost and it's location on the map should be determined by the Lat/Long. I have the Lat/Long data, cost and site id... I tried using MAPS.USCITY since it appears to have both LAT LONG and X Y (projected Lat/Long in radians). My dataset has both Lat/long and I calculated X Y in radians as well. But when I assign USCITY for the Map Data source and my table for Response data source, the only ID Variable(s) Role that shows up are the STATECODE and LAT/LONG. At first I thought that would be ok, because it would just use the lat/long, but all I ended up with is a blank result report - no map - nothing! I have read a number of documents concerning the mapping but haven't seen anything that indicates that this is possible. I find that hard to believe, so i must be missing something. Any pointers to the appropriate documentation would be appreciated. Regards, Fred
... View more
06-24-2014
11:48 AM
Thanks Haikuo; I suspected as much. That's ok, I am comfortable with writing SAS code and Macro's. I was just hoping that the STP wizard would make my life easier. Thanks for confirming though. Fred
... View more
06-24-2014
10:24 AM
Sorry Hai.Kuo I thought in the context of the heading that it was clear. What I am referring to is the conditional branching within EG5.1. I have a result table and based on a PROMPT (user selects which report to output), then a specific Pareto Chart is run. But when I try to turn that into a STP, I get a message: One or more items in the process flow cannot be included as part of the SAS program.... And looking at the code generated by the STP wizard, I see: /* --- Problem: Unable to express task in SAS code. --- */ Hopefully I have explained myself clearly enough. If not, let me know. You can't read my mind because it's a jumbled mess, you would just get lost . Regards, Fred
... View more
06-24-2014
09:52 AM
Hi all, During my search I found a discussing indicating that Conditional Processing code was not exported to a STP in EG 4.2 but was being looked at for a future release. Based on my quick attempt at a STP it appears that conditional processing is still not supported in EG 5.1. Can anyone confirm this? Or am I doing something wrong (entirely likely ). Thanks, Fred
... View more
06-19-2014
01:03 PM
Cynthia, Thanks! Your tips were invaluable. You were right about the output in EG. I was assuming that since the data was being displayed in EG, that it would be the same data pushed to the clients. Once I created an output report, then it worked! Thanks again.
... View more
06-18-2014
11:44 AM
Jaap, oh just contact the the sas platform admin. LOL Everyone makes that assumption - that if you have SAS, it is a big production oriented system with dedicated admin team. Unfortunately, that is not our case. We are using SAS in an engineering environment. We are working on solving engineering and ad-hoc problems using data analysis with SAS. Because of this, we are a very small team of engineers who convinced management to have IT stand up servers for us to use for our SAS server configuration. Our IT group is "difficult" to work with to put it mildly. Other than standing up the servers, they have nothing to do with the servers from a SAS perspective. We are our own admin (with no training). So learn as we go. We have convinced management to include budget next year to contract out the SAS admin function - but that is next year. So back to my original question....
... View more
06-18-2014
11:35 AM
Jaap, I am using the SAS Microsoft Add-in for Excel. I can run the STP from Excel, that part is easy, but not getting any results pushed to Excel. I was trying to push the data table results to Excel using the STP , but I get nothing. So far, I am not seeing any clear documentation on settings with the EG STP wizard to enable the results to be pushed to Excel or JMP. Regards, Fred
... View more
06-18-2014
11:21 AM
Hi Cynthia, Yes I have looked at the STP Developers Guide (9.3), but as I said before, it seems to be targeted to writing the STP code and not creating a STP from an EG process flow. I have been reading everything I can both in the SAS documentation and the web. Keeping in mind, we just got our sas server set up this week, so it's only been a few days. I will definitely be signing up for a STP class (recommendations of specific classes welcome). But in the mean time, I am testing the waters with some simple STP's. What my first process flow is doing: Working through a series of cascading prompts with dependencies to select a specific device and then joining that result with a larger fault data table and then prompting for a date range to filter on. The resulting table is a list of faults for that specific device over a filtered date range. The process flow works. How EG turns that into a STP? Not sure what you mean, unless you are referring to the wizard and the various screens. On pg3 of the wizard I select SASApp as the application server, Server type = default server, Source Code location & execution = allow execution on other app servers(store src code in metadata), Result Capabilities = Package Pg4 - librefs are set appropriately for the reference tables Pg5 - Prompts - edited the prompts to include dependencies. Pg6 - Data Sources & Targets - currently nothing set (no need for Data sources, since sources are local datasets) I have looked for documentation on the Data Targets and setting those, but the documentation seems thin and ambiguous. Not clear if and how to set this appropriately for various targets (ie. JMP & Excel) When I save and run the STP from EG, the cascading prompts work as expected, based on the log, everything appears to work, but the output of the STP in EG is a blank SAS Report. I can contact Tech Support, but I was rather hoping there would be documentation or examples more directly related to STP in EG 5.1 and more details on the wizard settings. Regards Fred
... View more
06-18-2014
09:33 AM
Hi everyone, Just getting started with SAS and stored processes. I have question on generating outputs from a stored process created in EG 5.1. First off, I have googled my #$$ off and documentation seems to be lacking or I am not finding it. I see lots of documentation covering writing the STP code, but I would appreciate it if any one can point me to clear examples or documentation of using EG 5.1 to create a stored process that can be run by Excel or JMP and auto-populate with the data results from the STP. I created a simple EG process flow which uses prompts to filter down a couple of larger data sets into a single table. I then created the STP from that and modified the prompts to provide cascaded prompts with dependencies. All that seems to work, but when I run the STP, it spits out an empty SAS Report. Not clear as to why. I assume it has something to do with Data Sources and Targets - but again, the documentation on this topic is worse than anemic. Any clear examples on setting this up for different types of data targets would be appreciated. Generally speaking, we are mainly concerned with pushing data to either JMP or Excel. Thanks, Fred
... View more
04-08-2014
04:31 PM
ta I get all the fun! :smileyconfused: I have a task which conceptually is fairly easy, but difficult in practice. I have thousands of Excel files that consist of many tabs or worksheets. The files were created as forms for customers to fill out and return - so there are locked fields, irregular data locations, multiple rows as column headers but not necessarily starting at row 1. There are a lot of legacy forms before someone finally decided that "hey, maybe it would be a good idea to standardize". After that point, there are files with a revision control (although so far, I have identified at least 20 different revisions). Way to many files to manually process and originally I started with Python because I don't see an easy way to do this in SAS. The process in Python is pretty straight forward, but.... (and there is always a but!) the Python package uses the Open Office API to open the Excel files and it throws an error when fields or tabs are protected or locked. So it looks like Python isn't going to work for a vast majority of the files. Here is a subset of my Python code :
for f in filelist[:]: created = time.ctime(os.path.getmtime(f)) modified = time.ctime(os.path.getctime(f)) try: book = xlrd.open_workbook(f) sheets = book.sheet_names()
if sheets[1] == '1-Summary': sheet = book.sheet_by_index(1) cellC1 = sheet.cell_value(0,2)[:14] else : cellC1 = 'Not valid Rev doc' newline = f + ',' + sheet.name + ',' + created + ',' + modified + ','+ cellC1 + '\n' VerList.write(newline) except Exception: ExLst.write(f) ExLst.write('\n')
This code loops through all the Excel files found in the subdirectories and opens them, checks for a sheet labelled '1-Summary' and then grabs for a text field out of a specific field which should contain the Revision control number. This produces two csv files - VerList : contains list of the readable files with the version control field listed and an exception list of files which would not open. Once that list exists, then I can select all the files of a specific version. The data on various tabs and various locations is consistent for any given version. I have found that I can open the Excel files where Python will not, so it should work. But after numerous google searches, I have yet to see a clean way to determine what sheets exist and then if a specific sheet exists, import but import based on specific Row/Col limits. If anyone knows of a white paper or posting along those lines, it would be greatly appreciated. Oh, and this could be Base SAS or EG, but I do have to loop across many thousands of files. Thanks! FG
... View more
06-12-2013
05:10 PM
Whew, that did it! proc sql; create table work._temp as select datetime, date FORMAT=date9., valueX from myDataset where date = "%scan(%bquote(&date_list), &i, %str(,))"d order by datetime; quit; I am finding that dealing with date and time formats within SAS is a royal pain in the ....... Thanks for your suggestions!
... View more
06-12-2013
04:59 PM
Reeza, I changed the format as you suggested to pull the distinct list, but still having issues matching the literal format in the follow up query. I changed the follow up query to : proc sql; create table work._temp as select datetime, date FORMAT=date9., valueX from myDataset where date = %scan(%bquote(&date_list), &i, %str(,)) order by datetime; quit; and I get the error: NOTE: Line generated by the macro function "SCAN". 1 24APR2013 ------- 22 76 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, GROUP, GT, GTT, HAVING, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. ERROR 76-322: Syntax error, statement will be ignored. Can you not put dates in macro variable list?
... View more
06-12-2013
04:02 PM
1 Like
I have a very large time series dataset where I am trying to generate plots grouped by day. Because of the size of the data, I cannot do BY without running out of memory. So my thought was to create a macro that cycles through distinct dates and creates temporary subsets of data by day, then creates various plots by day. My dataset key fields: datetime date valueX 23APR13:14:47:00 2013-04-23 48.6 My Code: proc sql noprint; select distinct date /* also tried DATEPART(datetime)FORMAT=YYMMDD10. */ INTO :date_list separated by "," from myDataset; quit; /* if I use %PUT &date_list, the data looks correct - matches the date and format of the date field */ %Let i =1; /* Will do a macro do while loop, but have to get the first proc sql working first */ %PUT %scan(%bquote(&date_list), &i, %str(,)); proc sql; create table work._temp as select datetime, date, valueX from myDataset where date = %scan(%bquote(&date_list), &i, %str(,)) order by datetime; quit; The proc sql query runs but 0 rows returned. Obviously the date is not matching the %scan. But when you look individually, it appears as if they match. I have tried a number of other formatting options. Any suggestions? Thanks, Fred
... View more