Maybe Im miss reading something but wouldnt means or univariate give you the mode down the column and not across the columns? I dont know of mode function in the datastep and to get (as I understand it) from univariate or means I think you would have to transpose the data. EJ ... View more

First pass through the code looks fine. Have you tried it? Are you getting errors? You are right that the benefit / bane of passthrough sql is the ability to write SQL native to the database source syntax. Great if you dont need a SAS specific function to pull the data. EJ PS -- couldnt tell if from the code / post if the odbc connection was already been used ie ODBC is created and there is a license for SAS/ACCESS interface for ODBC. ... View more

That was exactly what I was thinking as well. Have you tried assigning the value in a new data step. I am also assuming that your variable assignment code is actually something similar to: var_I_want_populated = "&random_var_name" ; EJ ... View more

Another thing that wasnt fully apparent (at least for us) was to use the UNC paths addresses for any libnames on the network instead of mapping drive letters. If you are not logged in the mapping is not used. Not sure if this is the issue or not but wanted to chime in just in case. EJ ... View more

Do you have the hierarchy in a table or defined somewhere? What happens if someone buys fruits and juice which is first? If all the items have a defined order say in a spreadsheet, you can read that into sas and merge the sequence into the input dataset and then sort the data by customer, date, and sequence. EJ ... View more

Charlotte, I guess the real question is ,,, who is defining the codes. If you are then a system like suggested should work. However if the codes are dictated else where, you are going to have to bring them into sas in some fashion either through directly writing them in the code (through cards or hand written format) or through read in of a spreadsheet or table elsewhere. What I was not quite understanding (may just be me), you say you dont want to have multiple if then statements but your do loop seems to be doing just that since you are looping through all the codes.It looks to me just gaining less coding but no gained processing efficiency. The format method or just a plain ol merge would eliminate the if then statements all together provided you can get the lookup table into sas. EJ ... View more

I think im catching on ... so you are using hash somewhat like an index in order to subset the large dataset. I think at this point my hash knowledge has been exhausted. I think if I was trying to do this I might start with a 20% random sampling of the large dataset stratified by equip id and date (or whatever the summary groupings are). That should give you a small enough dataset to do the summaries on but not take a day for proc summary to run. Of course you would have to run through the data again to set the sample. I might be leading down the wrong path so I will wait to see if others respond. EJ ... View more

I am pretty sure hashes are only available during the sas session and to save them out would mean to create a sas dataset from them which I dont think is your intent (but maybe it is). Is the data static? Or is it continuously updated? If its being updated then maybe a filter view through proc SQL might be better. The approach you take may also be dependent on whether this is a one time task or if it will be repeated on some interval. Brute force method might be fine for a one time thing, but for repeated tasks a more efficient process is probably desired. EJ ... View more

I am still learning Hash objects as well so I will be interesting in other comments but my understanding is they have to fit in memory or they cannot be used. Basically that is where the efficiency are gained because of the bypass of writing results to disk. I have to be honest and say I havent worked with files of the TB nature and not knowing a lot about the file structure there might be ways to reduce the on disk size to a more manageable size such as looking at any variable lengths and see if there are a lot of empty space (ie character fields that are larger than needed, numeric fields where precision is not needed so less than 8 length could be used). But even tricks like that may not reduce enough and would meaning running through all the data to adjust these, in which case you might as well run proc summary through the dataset and save the results to a dataset that you can report from. Alternatively, you could add an index to the file but that will also take time and additionally disk resources to store. Very interesting question ... interested to hear other responses. EJ ... View more

It isnt part of the merge on a data step but here is a reference and code for catresian in a datastep 24652 - Generate every combination of observations between data sets data every_combination;   /* Set one of your data sets, usually the larger data set */   set one;   do i=1 to n;     /* For every observation in the first data set,    */     /* read in each observation in the second data set */     set two point=i nobs=n;     output;   end; run; Hope that helps! EJ UPDATE -- Richard is right -- learning aside -- I did cartesians in SQL only ... View more

I dont have hard evidence for this and it might also fall under the category of "your mileage might vary", but given the gains in processing power and memory, SAS is almost always bottle necked at I/O (ie writing info to disk, temporary or permanently). Given this the less writing to disk you do the better, so sub queries should reduce some of the I/O. I have seen this in my case (complex queries from relational databases) but as mention by it probably depends on what the task is. Some of our more complex queries are made because we are writing / verify queries for a SQL Server Application our Developers are making, which means the query needs to be as platform independent as possible (ie no data steps or procs). I would say even after 10 years of writing SQL code in SAS, I rarely start off building a complex query directly but build the pieces and see if it makes sense to combine, just because I find it easier to track down where I have gone wrong that way. I do believe there is a trade off between readablilty of the code and using highly complex SQL queries (4 or more levels deep). I would be interested in hearing others comments. EJ ... View more