topic Re: Adjusting Numbers in SAS Programming

Adjusting Numbers

A_Halps — Wed, 13 May 2020 15:08:16 GMT

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

Re: Adjusting Numbers

ed_sas_member — Wed, 13 May 2020 16:00:06 GMT

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;

Re: Adjusting Numbers

biopharma — Wed, 13 May 2020 16:11:38 GMT

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 ;

Re: Adjusting Numbers

A_Halps — Wed, 13 May 2020 16:42:52 GMT

Thank you!