@Kurt_Bremser @PeterClemmensen @hhinohar
Hi,
Below is an example of data I have.
My requirement is, If my parameter is B then value = Death, but in this case the row is blank however there is a death value in some other row.
It doesnt matter in which row the value is in but the code should look in all rows for an ID and assign the value.
I want this to be in a data step as I have numerous if statements to process and there may be multiple ID's but the transaction rows are always empty, code should look at all rows beyond transaction 3 to find the value.
Data I have
ID | Transaction | Age | Death | Parameter | Value |
6 | 1 | . | . | A | |
6 | 2 | . | . | B | |
6 | 3 | . | . | C | |
6 | . | 3 | 3.45 |
Data I want
ID | Transaction | Age | Death | Parameter | Value |
6 | 1 | . | . | A | |
6 | 2 | . | . | B | 3.45 |
6 | 3 | . | . | C | |
6 | . | 3 | 3.45 |
Please advise.
Thank you
OK. Then dow loop provided might need a modification.
*case of multiple death obs;
data have;
infile datalines dlm="09"x;
input ID $ Transaction Age Death Parameter $;
datalines;
6 1 . . A
6 2 . . B
6 3 . . C
6 . 3 3.45
8 1 . A
8 2 . B
8 3 . C
8 . 2 12
8 . 2 11
8 . 2 10
9 1 . A
9 2 . B
9 3 . C
9 . 2 11
9 . 2 12
9 . 2 13
9 . 2 11
;
run;
*1;
data want;
do until(last.id);
set have;
by id;
v=max(death,v);
end;
do until(last.id);
set have;
by id;
if Parameter eq 'B' then
value=v;
else call missing(value);
output;
end;
drop v;
run;
Hi
I have multiple Id's and transaction 1,2,3 are always blank. I only need to look for values in other than (1,2,3) rows. I may have any number of rows after transaction 3 but i need to look into all the rows and assign the value.
ID | Transaction | Age | Death | Parameter | Value |
6 | 1 | . | . | A | |
6 | 2 | . | . | B | |
6 | 3 | . | . | C | |
6 | . | 3 | 3.45 | ||
8 | 1 | . | A | ||
8 | 2 | . | B | ||
8 | 3 | . | C | ||
8 | . | 2 | 12 | ||
8 | . | 2 | 12 |
SQL seems a good method here.
If you take the maximum of death column and only assign it to B that should work.
proc sql;
create table want as
select *, case when parameter = "B" then max(Death) end as ValueWanted
from have
group by ID
order by ID, transaction;
quit;
Untested - if this doesn't work, post the code and log and a description of what isn't working.
@bharath86 wrote:
Hi
I have multiple Id's and transaction 1,2,3 are always blank. I only need to look for values in other than (1,2,3) rows. I may have any number of rows after transaction 3 but i need to look into all the rows and assign the value.
ID Transaction Age Death Parameter Value 6 1 . . A 6 2 . . B 6 3 . . C 6 . 3 3.45 8 1 . A 8 2 . B 8 3 . C 8 . 2 12 8 . 2 12
@bharath86 wrote:
I have tested your code it works but this doesnt serve my purpose using sql, It has to be in a data step in order to process all the other statements.
Take the output from the SQL example and use that as input to a data step.
Insisting on solutions that use a single data set may be much more complicated then getting the pieces you need from another procedure and then additional processing.
data have;
input ID $ Transaction Age Death Parameter $;
infile cards4 truncover;
cards;
6 1 . . A
6 2 . . B
6 3 . . C
6 . 3 3.45
;
run;
data want;
do _n_=1 by 1 until(last.id | death ne .);
set have;
by id;
v=death;
end;
do _n_=1 to _n_;
set have;
if Parameter eq 'B' then
value=v;
else call missing(value);
output;
end;
drop v;
run;
This seems like a look up query.I would use a hash in this case.
data have;
input ID $ Transaction Age Death Parameter $;
infile cards4 truncover;
cards;
6 1 . . A
6 2 . . B
6 3 . . C
6 . 3 3.45
;
run;
data want;
length ID $ 8 Transaction Age Death 8 Parameter $8;
if _N_=1 then do;
*keep id and death variable where variable stores data;
if 0 then set have(drop=transaction age rename=(Death=value) where=(value));
dcl hash h(dataset:"have(drop=transaction age rename=(Death=value) where=(value))");
h.definekey("id");
h.definedata("value");
h.definedone();
end;
set have;
*look up have dataset so that all related transaction be filled;
_iorc_=h.find();
if parameter ne "B" then value=.;
run;
ID | Transaction | Age | Death | Parameter | Value |
6 | 1 | . | . | A | |
6 | 2 | . | . | B | |
6 | 3 | . | . | C | |
6 | . | 3 | 3.45 | ||
8 | 1 | . | A | ||
8 | 2 | . | B | ||
8 | 3 | . | C | ||
8 | . | 2 | 12 | ||
8 | . | 2 | 12 |
Hi @bharath86
It works fine here.
If values keeps missing for entire observation then length statement might be doing something.
data want;
if _N_=1 then do;
if 0 then set have(drop=transaction age rename=(Death=value) where=(value));
dcl hash h(dataset:"have(drop=transaction age rename=(Death=value) where=(value))");
h.definekey("id");
h.definedata("value");
h.definedone();
end;
set have;
_iorc_=h.find();
if parameter ne "B" then value=.;
run;
If this is not the case, please send log.
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!
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.