Hi, Thanks for the code. Much appreciated. I think, it would not be helpful to have data from the previous step to generate the cumulative percent variable. That is because, after creating the "Freq" variable using PROC FREQ we need to sort the data by the "Freq" variable in descending order and then calculate cumulative frequency and cumulative percent. We need to use some data steps to create those after sorting Freq. Secondly, can we do the whole thing using a BY variable? I have the same data for 3 different years and want to overlay the three graphs on one panel. ... View more

proc glimmix data=tre; class l2 id period; model count=period knt1-knt3 / dist=poisson link=log offset=offset; random intercept / subject=l2; random intercept / subject=id; run; I have also attached a sample of my dataset. ... View more

Thanks Jil. The model runs for ever and not converge. ... View more

Thanks Patrick. I am happy to run an additional proc sort. For some reason PROC SQL is taking for ever. Is there any way to calculate distinct values in a DATA step whcih should be equivalent to proc sql; select count(distinct id) as dist_id from test5 (where=(dur<10)) ... View more

Hi Patrick, Please see below. I didn't realize before that proc sql would take that long. Is the source data already sorted by id and gr? No - Are id and gr also numerical variables in your actual data? Yes - How much memory do you have available in your session? (proc options group=memory;run;) SAS (r) Proprietary Software Release 9.4 TS1M8 Group=MEMORY SORTSIZE=1073741824 Specifies the amount of memory that is available to the SORT procedure. SUMSIZE=0 Specifies a limit on the amount of memory that is available for data summarization procedures when class variables are active. MAXMEMQUERY=0 Specifies the maximum amount of memory that is allocated for procedures. MEMBLKSZ=16777216 Specifies the memory block size for Windows memory-based libraries. MEMMAXSZ=2147483648 Specifies the maximum amount of memory to allocate for using memory-based libraries. LOADMEMSIZE=0 Specifies a suggested amount of memory that is needed for executable programs loaded by SAS. MEMSIZE=2147483648 Specifies the limit on the amount of virtual memory that can be used during a SAS session. REALMEMSIZE=0 Specifies the amount of real memory SAS can expect to allocate.   - Do you really need variables dur and stay or would staykeep suffice? Yes, I need them         ... View more

HI, Thanks for the code. Would you please explain what do the 1. r. etc mean? My working dataset is quite big (~0.5 billion rows) and I found proc sql performing much slower than a data step. Is there any way to do the same in a single data step? ... View more

Hi, Sorry for the hassle. I posted a complete have data with working data step but it didn't go through. Please see it below. data test2; input obs id gr dif count; datalines; 1 1 1 1 1 2 1 1 1 1 3 1 1 1 1 4 1 1 1 1 5 1 1 1 1 6 1 1 1 1 7 1 1 1 1 8 1 1 1 1 9 1 1 1 1 10 1 1 1 1 11 1 1 1 1 12 1 1 1 1 13 1 1 1 1 14 1 1 1 1 15 1 2 3 1 16 1 2 1 1 17 1 2 1 1 18 1 2 1 1 19 1 2 1 1 20 1 2 1 1 21 1 2 1 1 22 1 2 1 1 23 1 2 1 1 24 2 1 1 1 25 2 1 1 1 26 2 1 1 1 27 2 1 1 1 28 2 1 1 1 29 2 1 1 1 30 2 1 1 1 31 2 2 6 1 32 2 2 1 1 33 2 2 1 1 34 2 2 1 1 35 2 2 1 1 36 2 2 1 1 37 2 2 1 1 38 2 2 1 1 ; run; ... View more

Thanks for the link. I have some experience with the first. and last. This situation is a bit more complicated as i need to use multiple BY statemants with different number of BY variables. ... View more

I am happy to do all those in one data step if possible. ... View more

Yes- I already got the expected results. But it took me to run 3 separate proc sql, I want to do it in one. ... View more

proc sql; create table test3 as select *, SUM( count ) as dur from test2 group by id,gr ; quit; proc sql; create table test4 as select *, case when dur>11 then 1 end as stay from test3 ; quit; proc sql; create table test5 as select *, max(stay) as staykeep from test4 group by id ; quit;     ... View more

I am not sure but need to create all those three variables. ... View more