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
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;
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;
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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.