Hello - I have a dataset that is so simple and what I want out of it is also conceptually simple, but I can't figure out how to approach in SAS. I would love to say I have tried to code it, but I am a relative SAS novice and am stumped. I imagine it might use some some sort of array procedure, with which I am unfamiliar. Any help/suggestions would be greatly appreciated!
I have an outcome measure of walk outcomes and dates for these measures by subject. Long form. Simple.
The outcome I need is "confirmed increase in walk outcomes". This is defined as a greater than 20% change from the first walk measure, that is confirmed with a same or higher walk measure at a future time point that must be >=90 days from the first date of change. There can be decreases or increases during the time points that precede or follow this confirmation measurement.
I seek a broad form of data for each subject - did they have a change, was this change confirmed (as per the above definition), and the date this occurred (the date refers to the first date of the change, not the date when the increase was confirmed). So I guess it is simple but it is not to me. 🙂
Any help is appreciated. I have attached a "have" and "want" file.
Thank you!
Anissa
Sounds like you just need to retain the first value.
Next time just paste the data text. It would probably take you less time that it took to generate and upload an Excel file and it will definitely make it easier for people to see your data and use it to make an example program.
data have;
input ID :$20. DAY OUTCOME ;
cards;
MSOAC/0016 1 8.55
MSOAC/0016 15 7.85
MSOAC/0016 28 6.95
MSOAC/0016 43 6.65
MSOAC/0016 57 6.5
MSOAC/0016 64 6.18
MSOAC/0016 78 7.25
MSOAC/0019 1 6.1
MSOAC/0019 93 6.2
MSOAC/0019 184 6.1
MSOAC/0019 263 6.3
MSOAC/0019 360 5.75
MSOAC/0019 451 6
MSOAC/0019 626 6.3
MSOAC/0019 724 7
MSOAC/0019 808 7
MSOAC/0019 899 5.85
MSOAC/0019 989 5.75
MSOAC/0019 1088 5.85
;
Also make sure the example actually has cases that you are trying to detect. From your description of looking for a 20% increase none of your data comes anywhere near that level of increase.
data want;
set have ;
by id day;
if first.id then call missing(base,first_day,first_90);
if first.id then base = outcome;
retain base;
change = outcome-base;
change_p = change/base ;
if change_p >= 0.20 then do;
first_day=coalesce(first_day,day);
retain_day;
if day-first_day >= 90 then first_90 = coalesce(first_90,day);
retain first_90;
end;
run;
first_ Obs ID DAY OUTCOME base day first_90 change change_p 1 MSOAC/0016 1 8.55 8.55 . . 0.00 0.00000 2 MSOAC/0016 15 7.85 8.55 . . -0.70 -0.08187 3 MSOAC/0016 28 6.95 8.55 . . -1.60 -0.18713 4 MSOAC/0016 43 6.65 8.55 . . -1.90 -0.22222 5 MSOAC/0016 57 6.50 8.55 . . -2.05 -0.23977 6 MSOAC/0016 64 6.18 8.55 . . -2.37 -0.27719 7 MSOAC/0016 78 7.25 8.55 . . -1.30 -0.15205 8 MSOAC/0019 1 6.10 6.10 . . 0.00 0.00000 9 MSOAC/0019 93 6.20 6.10 . . 0.10 0.01639 10 MSOAC/0019 184 6.10 6.10 . . 0.00 0.00000 11 MSOAC/0019 263 6.30 6.10 . . 0.20 0.03279 12 MSOAC/0019 360 5.75 6.10 . . -0.35 -0.05738 13 MSOAC/0019 451 6.00 6.10 . . -0.10 -0.01639 14 MSOAC/0019 626 6.30 6.10 . . 0.20 0.03279 15 MSOAC/0019 724 7.00 6.10 . . 0.90 0.14754 16 MSOAC/0019 808 7.00 6.10 . . 0.90 0.14754 17 MSOAC/0019 899 5.85 6.10 . . -0.25 -0.04098 18 MSOAC/0019 989 5.75 6.10 . . -0.35 -0.05738 19 MSOAC/0019 1088 5.85 6.10 . . -0.25 -0.04098
Hi @anissak1
Could you please paste your dataset set here? Just 2 records and explain the output you want. It is quite difficult to get your problem here
Thanks very much for your reply! The have/want view of the data is in the excel file. I have also posted a truncated sas data file just now. Just two columns of data plus the ID. It seems maddeningly simple but so daunting to me.
Any direction is greatly appreciated.
Anissa
I will also try to restate the analysis I need to perform.
Identify walk times that change >=20% change from the subject's first walk measure (I can do this but then am stumped!)
Identify a walk time after this initial change that is the same value or higher and that occurs >=90 studydays from this initial change.
I seek a broad form of data for each subject - did they have an initial change, was this change confirmed (as per the above definition), and the date this occurred (the date refers to the first date of the change, not the date when the increase was confirmed).
I hope this helps. Thank you!
Sounds like you just need to retain the first value.
Next time just paste the data text. It would probably take you less time that it took to generate and upload an Excel file and it will definitely make it easier for people to see your data and use it to make an example program.
data have;
input ID :$20. DAY OUTCOME ;
cards;
MSOAC/0016 1 8.55
MSOAC/0016 15 7.85
MSOAC/0016 28 6.95
MSOAC/0016 43 6.65
MSOAC/0016 57 6.5
MSOAC/0016 64 6.18
MSOAC/0016 78 7.25
MSOAC/0019 1 6.1
MSOAC/0019 93 6.2
MSOAC/0019 184 6.1
MSOAC/0019 263 6.3
MSOAC/0019 360 5.75
MSOAC/0019 451 6
MSOAC/0019 626 6.3
MSOAC/0019 724 7
MSOAC/0019 808 7
MSOAC/0019 899 5.85
MSOAC/0019 989 5.75
MSOAC/0019 1088 5.85
;
Also make sure the example actually has cases that you are trying to detect. From your description of looking for a 20% increase none of your data comes anywhere near that level of increase.
data want;
set have ;
by id day;
if first.id then call missing(base,first_day,first_90);
if first.id then base = outcome;
retain base;
change = outcome-base;
change_p = change/base ;
if change_p >= 0.20 then do;
first_day=coalesce(first_day,day);
retain_day;
if day-first_day >= 90 then first_90 = coalesce(first_90,day);
retain first_90;
end;
run;
first_ Obs ID DAY OUTCOME base day first_90 change change_p 1 MSOAC/0016 1 8.55 8.55 . . 0.00 0.00000 2 MSOAC/0016 15 7.85 8.55 . . -0.70 -0.08187 3 MSOAC/0016 28 6.95 8.55 . . -1.60 -0.18713 4 MSOAC/0016 43 6.65 8.55 . . -1.90 -0.22222 5 MSOAC/0016 57 6.50 8.55 . . -2.05 -0.23977 6 MSOAC/0016 64 6.18 8.55 . . -2.37 -0.27719 7 MSOAC/0016 78 7.25 8.55 . . -1.30 -0.15205 8 MSOAC/0019 1 6.10 6.10 . . 0.00 0.00000 9 MSOAC/0019 93 6.20 6.10 . . 0.10 0.01639 10 MSOAC/0019 184 6.10 6.10 . . 0.00 0.00000 11 MSOAC/0019 263 6.30 6.10 . . 0.20 0.03279 12 MSOAC/0019 360 5.75 6.10 . . -0.35 -0.05738 13 MSOAC/0019 451 6.00 6.10 . . -0.10 -0.01639 14 MSOAC/0019 626 6.30 6.10 . . 0.20 0.03279 15 MSOAC/0019 724 7.00 6.10 . . 0.90 0.14754 16 MSOAC/0019 808 7.00 6.10 . . 0.90 0.14754 17 MSOAC/0019 899 5.85 6.10 . . -0.25 -0.04098 18 MSOAC/0019 989 5.75 6.10 . . -0.35 -0.05738 19 MSOAC/0019 1088 5.85 6.10 . . -0.25 -0.04098
Hi Tom - What a beautifully elegant solution. I can hardly believe how simple and effective it is. I can for sure create my broad form of the data from these results and just did a QC to confirm I am getting the what I anticipated. Wow. I had not worked with the coalesce or call functions before.
Your answer makes me want to be a better coder. Either that or that I should leave my tough coding questions to the super heroes like you.
MUCH appreciation. Thank you!!!
Anissa
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.