Hi everyone I am beginner/new to SAS & learning for my certification. I was practicising on a particular dataset which has different variables and for some variables/columns there are "0s" as values. What I am trying to do is generate a table that lists the columns horizontally with the number of "0s" they have in their column. I have attached the dataset screenshot and also the output I am trying to achieve. ** I am using WPS environment for practising my codes. Any help/guidance will really be appreciated.   variable          label                       nmiss / count Total Amt        Total Amt                      0 Installments     Insss                            5 Fitness              fit                                8 ... View more

Assuming I understand what you mean.   data test1; input ssn loanNO effDate ddmmyy10. program $ ; if program='yes'; cards; 1234 1 01/01/2013 no 1234 2 01/05/2013 no 1234 3 01/01/2014 yes 5678 1 01/01/2012 no 9101 1 01/05/2014 yes 9101 2 01/09/2013 no ; run; data test2; input ssn fico date ddmmyy10. ; if month(date) in (2 5 8 11); cards; 1234 600 01/02/2012 1234 580 01/05/2012 1234 700 01/08/2012 1234 680 01/11/2012 1234 500 01/02/2013 1234 580 01/05/2013 1234 700 01/08/2013 1234 680 01/11/2013 1234 600 01/02/2014 1234 580 01/05/2014 1234 600 01/08/2014 1234 690 01/11/2014 ; run; data temp; set test1(in=ina rename=(effDate=date)) test2 ; by ssn date; retain prev prev_date; if first.ssn then call missing(prev,prev_date); if not missing(fico) then do;prev=fico;prev_date=date;end; next_date=intnx('year',prev_date,1,'s'); if ina; drop fico; format date prev_date next_date ddmmyy10.; run; data want; merge temp(in=ina) test2(rename=(date=next_date fico=next)); by ssn next_date; if ina; run; ... View more

Thanks so much! ... View more

Fortunately, the storm appears to have missed Toronto 🙂   ... View more

@Bhargav_Movva Sorry it means - means -1* cumtotsum + (-1*first.year * cumtotsum) + totalsum; This works. When first.year changes it equal to "1" and it makes cumtotsum =0. Otherwise first.year=0 and cumulative sum works. Year totalsum first.year cimsumtot ==== ======= ======= ========= 2014 10 1 10 2014 20 0 30 2015 10 1 10 2016 50 1 50 2016 60 0 110 2016 80 0 190 ... View more

Another way I thought this could be solved is-     proc sort data =have; by cust_id activity; run; proc transpose data = have out = have1 (drop = _name_); by cust_id; var days; id activity; run; Data want; set have1; if walk>= 90 and run >= 90 then cat = "walk_run"; if walk <90 and run >= 90 then cat = "runner" ; if walk >=90 and run <=90 then cat = "walker"; run; proc transpose data = want out = want1; by cust_id; var run walk; run; proc sql; create table want2 as select a.*, b.cat from want1 a inner join want b on a.cust_id = b.cust_id; run; data want3 (rename =( _name_= activity col1 = days)) ; set want2; run;   ... View more

Thanks a lot, Reeza!  ... View more

Thanks KSHARP. This one works as well, perhaps little better because there is no dependence on length. ... View more

Thanks for the solution, i will try this one too. ... View more

Thanks Chriznz and Ksharp. Both the solutions worked, i understood Ksharp's better as im not aware of the function used by chrisnz. I will dig deeper into it to learn more. ... View more

This problem was further solved in this thread.   https://communities.sas.com/t5/Base-SAS-Programming/Need-help-in-correct-use-of-retain-and-first-statement/m-p/303684#M64554  ... View more

In a way, RETAIN is required.  This statement is key:   total + total_months;   That form of statement (variable name + some numeric value) actually has a more complex set of meanings, one of which is to retain TOTAL.   Also note, if you only want a single observation per ID, the same DATA step can accomplish that.  Just before the RUN statement, add:   if last.medicaid_id;   Your SQL code looks like it works as well.     ... View more

do i=1 by 1 until (some condition);   This starts i at 1, and adds 1 each time through the loop.  It just doesn't set an upper limit for ending the loop, relying on the DO UNTIL condition to eventually become true. ... View more