DATA Step, Macro, Functions and more

How to derive an obs from a few obs above

Reply
Super Contributor
Posts: 307

How to derive an obs from a few obs above

Dear,

I trying a code that saves a few data steps . But I am not getting the output I need. Please suggest. Thank you

 

I have to check for           if  cd='p' and valc=t1 and cd='r' and valc=t2 and cd='s' and valc=t3 then "cd=t and valc='y' ;

if  if  cd='p' and valc not in ('t1' '')  and cd='r' and valc not in ('t2' '')and cd='s' and valc not in ('t3' '') then "cd=t and valc='n';

 

date for cd='t' should be populated from where cd='P' 

 

output needed;

id       CD                 valc                   date 

1        p                  t0                   2015-10-10
1         r                  t1                    2015-10-11
1        s                    t2                   2015-10-12

1        t                     y                       2015-10-10             
2      p                    a0                      2015-10-10
2       r                    a1                       2015-10-11
2      s                   a2                         2015-10-12

2       t                     n                      2015-10-10    

data one;
input id cd$ valc $ date $10.;
datalines;
1 p t0 2015-10-10
1 r t1 2015-10-11
1 s t2 2015-10-12
2 p a0 2015-10-10
2 r a1 2015-10-11
2 s a2 2015-10-12
;

proc sort data=one;
by id cd;
run;
data two;
   set one;
  by usubjid;
  array x{99999}$ _temporary_;
  array d{99999}$ _temporary_;
if first.id then do;n=0; d=0;call missing(of x{*});call missing(of d{*});end;
n+1; 
d+1;
x{n}=valc;
d(n)=date;
output;
if last.id then do;
		test='T';
		if x1="t0" and x2 ='t1' and x3 = 't3' then do; valc='Y';
adt=d1;end;
else if cmiss(x1,x2,x3)=0 then do; valc='N'; adt=d1; end;output; end; run;

 

Trusted Advisor
Posts: 1,825

Re: How to derive an obs from a few obs above

[ Edited ]
Posted in reply to knveraraju91

What do you mean by your line:

 if  cd='p' and valc=t1 and cd='r' and valc=t2 and cd='s' and valc=t3 then "cd=t and valc='y' ;

 

You can't have different values in a variable in the same observation.

You probably mean to check values per ID.

You missed quotes on checking valc values !

 

In such case I would retain flags, one per condition. Turn flags OFF at first.ID.

Turn specific flag according to positive condition .

When last.ID check whether all flags turned on and act according to result.

 

data want;
 set have;
   by ID;
       retain flag1 - flag3 svdt;
       array fl flag1 - flag3;
       if first.ID then do i=1 to 3; fl(i) = 0; end;
       
      if cd='p' and valc = 't1' then do; fl(1) = 1; svdt = date; end;
      if cd='r' and valc = 't2' then fl(2) = 1;
      if cd='s' and valc = 't3' then fl(3) = 1;
      output;   /* save input obs into output */

      if last.ID then do;
         if fl(1) + fl(2) + fl(3) = 3 then do;
            cd = 't' ;
            valc = 'y';
            date = svdt;
           output;   /* write a new obs to output */
         end;
        else do;
            cd = 't' ;
            valc = 'n';
            date = svdt;
            output;   /* write a new obs to output */
         end; 
    end;   /* end if */
run;        
Ask a Question
Discussion stats
  • 1 reply
  • 92 views
  • 1 like
  • 2 in conversation