I don't necessarily need a coding solution, but am looking to understand why this code doesn't work. data want; set have;    if id=lag(id) then last_visit=lag(visit); run; Have: id visit 2 7/5/2014 2 8/30/2014 2 9/24/2014 4 8 4/25/2014 8 4/26/2014 8 5/28/2014 Want: id visit last_visit_date 2 7/5/2014 2 8/30/2014 7/5/2014 2 9/24/2014 8/30/2014 4 8 4/25/2014 8 4/26/2014 4/25/2014 8 5/28/2014 4/26/2014 Actual Result: id visit last_visit_date 2 7/5/2014 2 8/30/2014 7/5/2014 2 9/24/2014 8/30/2014 4 8 4/25/2014 8 4/26/2014 9/24/2014 8 5/28/2014 4/26/2014 I believe I could also accomplish this with a retain statement, but it eventually worked after outright creating the variable lag_visit (below).   I don't completely understand why it was necessary to do this. data want; set have;       lag_visit=lag1(visit_dt);      if id=lag(id) then last_visit=lag_visit; run; Thanks, K ... View more

I want to figure out how to add up the office_yes indicator variable in two ways, then tack it onto the end of each line.      1. Per Visit      2. Per ID (only summing one 'yes' indicator per visit) I've tried a several combinations of CASE/WHEN, SUM, COUNT and COUNT(DISTINCT(id), as well as other things like sum(distinct(count(id)))," which gives the message "ERROR: Summary functions nested in this way are not supported."  Otherwise, this is as far as I've gotten: proc sql;   create table want as   select a.* ,   sum(office_yes_by_enc) as total_visits_per_id   from (select    visit_number,    CASE WHEN sum(office_yes)>=1 then 1 ELSE 0 end as office_yes_by_enc,    id,    office_yes   from have  group id, visit_number)  as a    group id ;quit; I know there is a straightforward solution with PROC SORT and merge, but there are millions of lines and I'd like to figure out how to do it with SQL. And is this possible to do one SQL procedure, or does it need to be broken up? Thanks! Have: id visit_number office_yes 8 59 1 8 59 . 8 10 . 8 19 . 2 51 1 2 70 1 2 45 . 2 45 1 2 45 1 2 35 . 2 62 1 5 87 1 5 66 1 Want: id visit_number office_yes office_yes_by_visit total_office_visits_by_id 8 59 1 1 1 8 59 . 1 1 8 10 . 0 1 8 19 . 0 1 2 51 1 1 4 2 70 1 1 4 2 45 . 1 4 2 45 1 1 4 2 45 1 1 4 2 35 . 0 4 2 62 1 1 4 5 87 1 1 2 5 66 1 1 2 ... View more

How could I create new observations in a dataset based on adding, subtracting, etc. across other observations? In the example below, observation 4 is created so that Var1 = 'NotTN' Var2 = sum of all Var2 where Var1^='TN' Have: Var1 Var2 SC 1 NC 3 TN 5 Want: Var1 Var2 SC 1 NC 3 TN 5 NotTN 4 Thanks! ... View more

I would like to create a variable that increases by 1 only when another variable satisfies some criterion. For example, in drug1, the stopdates are at the 2rd, 4th, and 6th visits; whereas with drug2, the stopdates are at the 3rd and 5th visits, and the stopdate counts from 1-2 with a missing at 3 since there is no 3rd time stopping the drug. The data should come out where: visitdate_1_2 = stopdate1_1 visitdate_1_4 = stopdate1_2 visitdate_1_6 = stopdate1_3 visitdate_2_3 = stopdate2_1 visitdate_2_5 = stopdate2_2                   . = stopdate2_3 The macro below is what I'm working with.  The [??????] is where I'm guessing a macro variable or function might go. %macro duration (n,m); data two; set one; %do i=1 %to &n.;        *drug number, up to 10; %do j=1 %to &m.;       *changes to each drug, up to 6; if changed&i._&j.='stop' then do stopdate&i._[???????]= (visitdate_&i._&j.); %end; %end; run; %mend; I attempted another do loop inside the other two, but it just wrote one date on top of the last, so they were all the same. Any suggestions?  Thanks! ... View more

How would I run a macro on a list of variables one at a time, without the macro reading all of them at once?  In this example, I am also trying to merge individual datasets back together based on the var list. This code doesn't work: %let vars = f15_specimen    f15_albumin_month    f15_albumin_year    f15_albumin_g; *Create individual datasets named by the variable; %macro variable; data &vars; set f15_long; keep thesubjectid zvisitdate_lab &vars; if &vars=. then delete; run; proc sort data= &vars; by thesubjectid zvisitdate_lab; run; proc sort nodupkey data= &vars; by thesubjectid; run; data all; merge  &vars; by thesubjectid; run; %mend; The idea is to select the first non-missing datapoint from each variable per SubjectID.  Here I'm using the long-form data; I have also attempted this problem within an array on wide-form data (ie. f15_specimen1-f15_specimen40), but succeeded only in selecting the last non-missing datapoint instead of the first.  So a solution using an array could work, although I would like to know how to work with lists in macros. Thanks! ... View more