That's because you also are estimating an intercept term. In a simple model with a single two level categorical predictor, the estimated mean value for the reference level (by default I believe this corresponds to the largest value of the categorical variable) is the value of the intercept term.   There should be an option to run the model without an intercept term. Something like /noint on the model statement or something like that.   You also should be able to get predicted means, by the values of all class variables, by using a MEANS or LSMEANS statement. Look into that. ... View more

@JJP1 wrote: ok what if it did not wait for unlocked file to be available and run the job with previous weekly available data.and what if the weekly dataset is not updated with latest records and yet to run may be just seconds.actually it is more of timing issue,it may occur and may not occure. sorry to annoy but kindly help do we have any other option or you mean that there is no other way except "FILELOCKWAIT" please.   This doesn't seem to be solely a file lock issue. It seems that you are saying that your data are updated and you want your process to not run unless the data have been updated. That is not a file lock issue. You'll have to institute some sort of process to check the update status of the data. How you do that depends on your circumstances. One way would be to check the file time stamp to see if the file was updated recently; "recently" depends on how often your data are updated. If it's once a day then you can check to see if the file was updated the day your program runs; if it wasn't then delay your program. You'd have to put some sort of while loop in your SAS code to delay execution until the timestamp changes. Alternatively, use batch scripting to do the checking and once the file has been updated submit your SAS code. Your SAS code would still need the code to handle file lock issues.     ... View more

@JJP1 wrote: not different dataset,same dataset weekdataset" please.you understood please But you said the process would read "old" data. If it's not reading old data, then what's the issue? It waits until the file is unlocked right?     ... View more

@JJP1 wrote: HI All, As part of monthly job i am copying weekly dataset say "weekdataset" to monthly dataset "monthdataset" on every 1st of month. there is also one more weekly job "selectname" which is using weekly dataset "weekdataset" at same time(it might or might not happen same time always but this issue  has occured only once)due to which i got lock error ""A lock is not available for vv.weekdataset.DATA" in weekly job "selectname",as this dataset is being used in monthly job to copy over. if we user FILE LOCK WAIT option blindly it will see the weekly dataset and run the job "selectname" with old data Actually the above issue has occured only once not always and it is more of timing issue,it may occur and may not occur please. so as part of this I am trying to fix permanently.please suggest or correct me. How will selectname use old data? Both applications are accessing a dataset named "weekdataset" and neither job, as you describe them, is updating "weekdataset." If you use FILE LOCK WAIT then how will that make selectname use a different data set than "weekdataset"? That makes no sense.     ... View more

Patrick's example code does not modify the source data set source.bbbbb It READS from that data set to create subsets for sorting.  His code then puts the sorted subsets back together NOT overwriting the source data set.   Also, see this paper on different sort methods with SAS: https://www.lexjansen.com/nesug/nesug13/29_Final_Paper.pdf   Optimal (in terms of time and/or disk usage) sorting depends on how much memory you have available and your disk I/O.     Since you seem to be short on storage space, consider using Indexes. See Example 3 in the linked PDF. You'll sacrifice running time for reducing storage space. Since you aren't modifying the LIVE data set directly, you'll need to make a copy of the data set and create the INDEX on that copy. If you can read the data set into memory (100+ gig memory) then you'll have really fast performance after copying the data to memory.   Futhermore, there are variations you can try. Assuming your keys are unique, you could use hash techniques to sort subsets of your data in memory and then combine the data back together[I think you need to be careful in how you'd split up your data set into pieces for creating indexes. You'd want to just be able to stack the sorted data produced from each subset.] This requires time to copy data to memory (and enough memory) but if disk storage or disk i/o is a big concern then it might give you better performance. I think using a hash object (or objects) will do better than INDEXES if you can't fit your entire data set into memory. ... View more

Also, this may be a stretch, but if your IML code is imbedded in a SAS macro then we've found that we cannot include submit blocks directly in the proc iml. We have to put the submit blocks in files that we %include within the proc iml call.   ... View more

Put quit;   at the end of your proc IML statement?     If that doesn't work, we also terminate our R commands with semi-colons. I'm not sure why we do that as semicolons are optional but you could try that and see if maybe R is having a hard time parsing your various R statements.       ... View more

We had some issues with java on Linux and extraordinary amount of memory usage with xlconnect which requires java like the xlsx package. We got it to work generally but it requires each user to set java memory limits. Openxlsx is java free and avoided that issue. ... View more

I think your life will be easier if you trying using R with SAS. At least that way SAS program(s) are submitted which produce your results.   You'll have to call R from within SAS (you can do that with SAS IML), pass your data to be written to R via SAS IML, and submit R code to R via SAS IML to write your data to the excel file. Use the openxlsx or XLConnect R packages.   I haven't seen any other solution that preserves the notion of SAS pushing data to excel.   See this paper for example code to get you started: https://analytics.ncsu.edu/sesug/2017/SESUG2017_Paper-109_Final_PDF.pdf ... View more

Well, SAS "now" isn't the tool for creating complicated excel spreadsheets. It used to be when DDE was readily available. Without DDE its capabilities are seriously diminished.   In particular, the push to use SAS on a grid with Linux means that VBA really isn't an option in terms of integrating SAS with calls to VBA which makes creating an integrated process to create the data with SAS and produce the tables with VBA annoying.     ... View more