Hello.
I have a specification for B1IND
Set B1IND to the value of A1IND where ABLFL equals "Y" within each USUBJID and PARAMCD. |
Here is the code
data have;
input SUBJID $ :$40. PARAMCD :$14. PARAMN: $2. A1IND :$40. ABLFL :$2. B1IND :$40.;
infile datalines dlm = '|';
datalines;
1001|HGB|1|Within range| |High
1001|HGB|1|High|Y|High
1001|HGB|1|Within range| |High
1001|HGB|1|Low| |High
1001|HGB|1|Low| |High
1001|HGB|1|Within range| |High
1001|HCT|2|No criteria defined for parameter| |No criteria defined for parameter
1001|HCT|2|No criteria defined for parameter|Y|No criteria defined for parameter
1001|HCT|2|No criteria defined for parameter| |No criteria defined for parameter
1001|HCT|2|No criteria defined for parameter| |No criteria defined for parameter
1001|HCT|2|No criteria defined for parameter| |No criteria defined for parameter
1001|HCT|2|No criteria defined for parameter| |No criteria defined for parameter
1001|WBCH|4|High| |Within range
1001|WBCH|4|Within range|Y |Within range
1001|WBCH|4|Within range| |Within range
1001|WBCH|4|Within range| |Within range
1001|WBCH|4|Within range| |Within range
1001|WBCH|4|Within range| |Within range
;
Ideally I would want to create a column for B1IND and have the baseline value (where ABLFL='Y') displayed for all rows per paramcd/paramn. I tried using the RETAIN/OUTPUT statement but it wasn't working as intended. Any suggestions?
Thanks appreciated. yes that was just a typo with the USUBJID and no just one record per parameter with a value of 'Y' for ABLFL but that is good thinking. Here is the code I settled on and works fine.
proc sql;
create table b1ind as
select distinct usubjid
,paramcd
,a1ind
from lbchg2
where ABLFL ='Y'
order by 1;
/* Merge b1ind back onto the original record*/
create table lbchg3 as
select a.*,
b.a1ind as b1ind
from lbchg2 a left join b1ind b on a.usubjid=b.usubjid and
a.paramcd=b.paramcd;
quit;
Can you add B1IND to your data set so we know what the expected output looks like?
No problem. I've updated the sample data EDIT: Apologies there was an error for the desired outcome for the last paramcd which I've fixed
From the example data posted I think that your requirement
Set B1IND to the value of A1IND where ABLFL equals "Y" within each USUBJID and PARAMCD. |
may perhaps be better stated is "If any ABLFL = 'Y' for a combination of USUBJID and PARAMCD then set B1IND to the value of A1IND from the record where ABLFL='Y'. Is this an accurate description or paraphrase of the requirement?
"Each" in the first statement would for many things mean that the record, not the group, is treated as such.
One additional question: do you ever have multiple values of ABLFL other than missing for a USUBJID and PARAMCD? If so do they have different values of A1IND? That would mean that you need to extend the rule.
If you have not checked that condition you might run code on your data set like this to get counts of combinations that actually occur in your data.
Proc freq data=have; where not missing(ABLFL ); tables USUBJID * PARAMCD *ABLFL *A1IND / list; run;
Additional comment: Your requirement shows use of the variable USUBJID variable. Your example data does not include that variable. When we have to guess that one variable is supposed be used in a different role/position/name then results can be off. So it is a good idea to make variable names match consistently.
Thanks appreciated. yes that was just a typo with the USUBJID and no just one record per parameter with a value of 'Y' for ABLFL but that is good thinking. Here is the code I settled on and works fine.
proc sql;
create table b1ind as
select distinct usubjid
,paramcd
,a1ind
from lbchg2
where ABLFL ='Y'
order by 1;
/* Merge b1ind back onto the original record*/
create table lbchg3 as
select a.*,
b.a1ind as b1ind
from lbchg2 a left join b1ind b on a.usubjid=b.usubjid and
a.paramcd=b.paramcd;
quit;
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.