Adjusting Numbers

[A_Halps]

Hi-

I am trying to adjust columns samh and demp so that they keep the highest number moving forward with the years. Can someone please help me figure out this code? I'm really struggling with figuring it out.

my table currently looks like:

Study_id   year   samh   demp

001            5          2          .

001            6          3          .

001            9          3          .

002            7          2          .

002            8          4          .

002           10         3          .

002           16         3          .

003           20         .          4

003           20         .          5

003           20         .          4

I want it to look like:

Study_id   year   samh   samh_adj   demp   demp_adj   final

001            5          2             2                .                .            2

001            6          3             3                .                .            3

001            9          3             3                .                .            3

002            7          2             2                .                .            2

002            8          4             4                .                .            4

002           10         3             4                .                .            4

002           16         3             4                .                .            4

003           20         .               .               4                4           4

003           20         .               .               5                5           5

003           20         2              2              4                5           5

Thanks,

Allison

[ed_sas_member · Accepted solution]

Hi @A_Halps 

You can do this:

data want;
	do until(last.study_id);
		set have;
		by Study_id;
		retain samh_adj;
		retain demp_adj;
		if first.study_id or samh_adj<=samh then samh_adj=samh;
		if first.study_id or demp_adj<=demp then demp_adj=demp;
		total = sum(samh_adj, demp_adj);
		output;
	end;
run;

 

[A_Halps]

Thank you!

[biopharma]

Many possible solutions. Here is one with the now popular DOW loop. 

data have ;
   infile datalines missover ;
   length study_id $5 year samh demp 8 ;
   input Study_id $  year   samh   demp ;
   datalines ;
001            5          2          .
001            6          3          .
001            9          3          .
002            7          2          .
002            8          4          .
002           10         3          .
002           16         3          .
003           20         .          4
003           20         .          5
003           20         .          4
;
run ;

data want ;
   do until (last.study_id) ;
      set have ;
      by study_id ;
      if nmiss(samh,samh_adj)<2 then samh_adj = max(samh_adj,samh) ;
      if nmiss(demp,demp_adj)<2 then demp_adj = max(demp_adj,demp) ;
      final = sum(samh_adj,demp_adj) ;  
      output ;
   end ;
run ;