SAS Support Communities

FredGIII · ‎05-21-2013

Oh by the way Ahmed, The NAS drive is dedicated to just my desktop with a dedicated gigabit ethernet, so I have the full bandwidth available. But I agree with you, long term, I am really looking forward to the Server SAS /SAN!

FredGIII · ‎05-21-2013

Ahmed, Chris, et. al.: Thanks for all your feedback. I feel a bit foolish. During my research it appeared to me that hash tables would have been best and I spent a lot of time trying to figure out that route. What I had not tried was just creating an index. I finally did that yesterday. Creating the index took about 12 hrs (long but at least not 3 days!). After the index was created, I was amazed at how fast subsetting and summarizing went. I did a trial of proc summary for one serial number by day after the index and it only took about 4 minutes!!!! So, now I have a macro to loop through the serial numbers and I have turned it loose . Lesson learned!

FredGIII · ‎05-20-2013

Ahmed, Thanks for the reply. Your assumptions are correct - I am running SAS on Win7 64bit, Quad core, 8GB ram and 24TB NAS drive. Based on your comments, it sounds like SPDE might not benefit me. I am in the process of indexing the dataset and I am hoping that will help speed up the subsetting process. In the mean time, I am having a hard drive shipped that contains the individual files for each turbine. We are also in the process of installing a Server based SAS installation, but that is probably a month from being complete. The good news... its a learning experience . FG

FredGIII · ‎05-20-2013

Gady, Thanks for the info. We'll look into SPDE and figure out how to set it up. That sounds promising. If you have links to specific documents explaining SPDE, it would be helpful . Otherwise, it will be google searching I go

FredGIII · ‎05-20-2013

HI Gady, It appears to me that the default for Memsize is 0 which allocates the maximum memory available. It is not clear to me what benefit changing the Memsize would have. I am not familiar with SPDE, but I have to assume that it is a separate product and I do not believe we have a license for it - so not a choice. As for subsetting the data into smaller datasets, I did try that, but it took 3 days to subset one serial number. With the number of unique serial numbers, at that rate it would take over 6 years to complete the file splits... talk about job security LOL. Fortunately, it appears that I can get a drive sent to me that contains the separate files by serial number. I'll have to import those as separate datasets and then use a macro to loop through the individual datasets and run proc means on each one separately. At this point, I don't see another realistic approach. If you have other ideas to try, I would love to try them though. Thanks, FG

FredGIII · ‎05-20-2013

Haikuo; I tried your approach and got an insufficient memory to execute data step program error. I liked your approach and learned a lot from your code. I was bummed when I got the message. I suspect I may have to go back to the data source and see if they can supply the data in segmented form. We are installing a server based SAS system and I probably could handle the file once that is done, but timing wise, that may be a bit late. So still trying to figure out a way to split it in a reasonable time frame. Thanks, FG

FredGIII · ‎05-20-2013

Before I try summarizing, I did write a short program to try and split the dataset into individual datasets for each equipmentsernum. Here is the program - please feel free to critique and offer suggestions on better programming : %macro SplitFile; /* get list of unique serial numbs */ proc sql noprint; select equipmentsernum into :sernum separated by " " from unique_serial_number; quit; /* second list of sernum with sn_ for valid sas dataset name */ proc sql noprint; select "sn"||"_"||equipmentsernum into :tag separated by " " from unique_serial_number; quit; %let i = 1; %do %while(%scan(&sernum, &i) ne); data %scan(&tag, &i); set MyLargeDataset; where equipmentsernum = "%scan(&sernum, &i)"; run; %let i= %eval(&i+1); %end; %mend SplitFile; The program works, but I let it run all weekend and in 58hrs it was just finished with one serial number. Would it be beneficial to use hash tables in conjunction with this macro to speed up the file split? Or do you still think summarizing the data with a subset of variables would be faster? Other ideas? Thanks FG

FredGIII · ‎05-17-2013

ballardw: If I understand you correctly, are you suggesting that I do a proc summary on a subset of the 290 variables in the table - as an example below of just 5 sensors. But perhaps break up the 290 variables into groups of 10 or 20? : proc summary data=MyLargeDataset nway; class equipmentsernum Date flag; Var sensor1 sensor2 sensor3 sensor4 sensor5; output out = SummaryDataset (drop = _type_ _freq_) Sum = max = min = median = mean = std = Kurt = Skew = n = /autoname ; run;

FredGIII · ‎05-17-2013

Reeza, I am running proc freq as you suggested now. I imagine it will take a while to get through the data (if it gets through all of it). Will report back when something happens. FG

FredGIII · ‎05-16-2013

Tom, In this case, the DATE variable is just that, MMDDYYYY. It was created as a subset of a date/time stamp for this reason. And running the proc summary above - we ran out of memory. We do have more memory on order, but have to wait on purchase order approvals, go to sourcing, purchasing..etc So I was hoping to find a quicker way to subset the data. If can subset the data quickly into tables by equipmentsernum then SAS can handle that file size without too much problem. Thanks, FG

FredGIII · ‎05-16-2013

Astounding: I did mention the date values above (every 5 min, so 288 observations or date values per day). So the summary function would have to summarize the 288 obs for each day for each equipmentsernum. We did try running the following : proc summary data=MyLargeDataset nway; class equipmentsernum Date flag; Var _numeric_; output out = SummaryDataset (drop = _type_ _freq_) Sum = max = min = median = mean = std = Kurt = Skew = n = /autoname ; run; We ran out of memory after 30 hrs. By the way, the flag is either 1 or 0 (1= full speed, 0= partial speed). Would it be better to do a By equipmentsernum, Date, flag instead of NWAY? FG

FredGIII · ‎05-16-2013

Thanks Astounding (that sounds strange ), We did think about doing PROC SQL, and we did try a test doing a subset using proc sql for one specific equipsernum, that query alone took about 30 hrs (a bit over 1 day). There are almost 800 unique ids in equipsernum, which means it would take over 2 years to subset the entire dataset.. I was hoping for something a bit speedier LOL.The sensor data is stored at 5 min intervals which means there are 288 observations per day. Thanks, FG

FredGIII · ‎05-16-2013

EJ - the data is static and we just need to generate summaries to create smaller datasets that we can work with. As for saving the hash table, my only thought was that if I am going to loop through by id and subset then proc summary, that I would either need to save the hash table or regenerate it for each loop. It seems that regenerating it for each loop would be inefficient. But again, that is assuming my approach is valid . FG

FredGIII · ‎05-16-2013

Ok before someone slaps me for doing something stupid, I realized that defineData(all:'y') with that large of a dataset was crazy. So I have removed that and am currently running the following against the 3TB dataset just to see if I can create the hash table. But my questions still hold - is there a better approach? Can I save the hashtable? Looping through hash table to subset the data? Links to hash table tutorials? Thanks, F: data hash_results; set myLargeDataset; if (_n_ eq 1) then do; declare hash a(dataset:'myLargeDataset', multidata:'y'); a.defineKey('equipmentsernum'); a.defineData('equipmentsernum', 'Date'); a.defineDone(); end; run;

FredGIII · ‎05-16-2013

Does anyone have links to a good beginning tutorial on Hash tables? I have been googling and reading a lot, but I find that the papers, so far, are fairly specific and vague enough that I find it difficult to understand the overall structure. I have a dataset with 530 million observations and 250+ columns of sensor data (~ 3 TB). The powers that be want stats summaries on ALL of the columns (n, min, max, mean, stddev, skew, kurtosis, var) by equipment id by date. Being new to SAS, I did a lot of research and it appears that hash tables would be the best approach but there are several aspects to the programming that are not clear to me. My initial approach (and please direct me if there is a better approach) is to use the hash tables to subset the data by id (or id/date) and then proc summary on the subset. I tried running the hash subset and ran out of memory (Win7 8GB memory). data hash_results; set myLargeDataset; if (_n_ eq 1) then do; declare hash a(dataset:'myLargeDataset'); a.defineKey('equipmentsernum', 'Date'); a.defineData(all:'y'); a.defineDone(); end; equipmentsernum = '296737'; if(a.find() eq 0); run; This code works on a subset of myLargeDataset, but on the big set, it quickly runs out of memory. Some things I haven't figured out with hash tables are: 1) Can I save the resulting hash table to re-use outside of the data step? 2) Can I write a macro to loop through the hash? My thought was to use the hash table to subset myLargeDataset into a smaller table of just one serial number or id, the call proc summary to get stats for that unit, then loop through the next serial number...etc. Any hash tutorials or pointers would be greatly appreciated. Regards, Fred

Online Status	Offline
Date Last Visited	‎12-17-2021 06:04 PM

SAS Support Communities

Re: ODS Graphics Designer Code - results not the same run in SAS Studi...

Re: ODS Graphics Designer Code - results not the same run in SAS Studi...

ODS Graphics Designer Code - results not the same run in SAS Studio

Where are SAS Studio Snippets stored?

Re: SGPLOT only using half the graph space (offsetmax default value)

SGPLOT only using half the graph space (offsetmax default value)

Re: Run SAS code remotely on a Linux server

Re: Overlay histograms - all data vs subset

Re: Overlay histograms - all data vs subset

Overlay histograms - all data vs subset

Re: ODS Graphics Designer Code - results not the same run in SAS Studi...

Re: Where are SAS Studio Snippets stored?

Re: SGPLOT only using half the graph space (offsetmax default value)

Re: VA - Date as parameter - Status changed to: Implemented

Re: STP stream to Excel - report increases data size

VA - Slider Object - set default values on open

VA - Date as parameter

VA - Slider Controls object option

Re: Hash Tables - problems with large datasets

Overlay histograms - all data vs subset

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Re: Hash Tables - problems with large datasets

Hash Tables - problems with large datasets

Follow Us

What is...