data T; input ID L CF CD EXP; Datalines; 1 1 A1 AAA 50 1 2 A1 BBB 100 1 4 A2 CCC 200 2 1 A1 AAA 50 2 2 A1 BBB 100 2 4 A2 CCC 200 ; proc sql; create table wanted as  select cf, sum (exp) as A from T group by distinct cd; quit; Hi. In this dataset I want to find to create a table sum of EXP per CF but only per the distinct values of CD i.e. the output should be A1 150 A2 200   Thank you.  ... View more

data T; input ID L EXP; Datalines; 1 1 10 1 2 20 1 4 20 1 5 20 2 1 10 2 2 60 3 2 50 3 3 50 ; data want; do until(last.id); set T; by id; if l<=1 then flag=1; end; do until(last.id); set T; by id; if not flag then exp=0; output; end; drop flag; run; Hi,   with this code, this is the correct output (per ID if First.ID and L>1 all the sequential Exp updated to 0) 1 1 10 1 2 20 1 4 20 1 5 20 2 1 10 2 2 60 3 2 0 3 3 0   How can this code be amended in order if per ID the sequence of L is broken, the rest Exp to update to 0? So the output should be:   1 1 10 1 2 20 1 4 0 1 5 0 2 1 10 2 2 60 3 2 0 3 3 0   Thanks. ... View more

data T; input ID L EXP; Datalines; 1 1 10 1 2 10 1 2 20 1 3 30 2 1 10 2 1 10 2 2 60 3 2 60 3 3 60 ; Hi, I would to like to update Exp to 0 if for First.ID and L>1 for all instances of this ID (the sorting is done) i.e. the output should be 1 1 10 1 2 10 1 2 20 1 3 30 2 1 10 2 1 10 2 2 60 3 2 0 3 3 0 ; ... View more

data T; input ID L EXP; Datalines; 1 1 10 1 2 10 1 2 20 1 3 30 2 1 10 2 1 10 2 2 60 3 2 60 ; data want; set t; by id; if first.id and first.L > 1 then exp=. ; run; Hi, in this sample I want to update exp to null in case that in every first ID the first L is >1 which is the case with ID = 3 Where is the mistake in the code?   Thanks for the help.  ... View more

data T; input ID L M EXP; Datalines; 1 1 100 10 1 2 100 20 1 2 100 20 1 3 100 30 2 1 50 10 2 1 50 20 2 2 50 60 ; Hi, based on this dataset I want to create a new dataset with an extra column (WANTED) which includes the following steps: 1. All calculations are per group ID 2. For each first ID occurrence, the value WANTED is equal to the value in Col EXP 3. For the second row and so on, the formula is IF L=1 then =EXP Else if (cumulative sum of previous values WANTED<MV) then MV-LAG(EXP) ELSE 0     Finally the WANTED column should have the following values 10 90 0 0 10 20 30   Thank you in advance for your help.   ... View more