topic Re: Populating value based on condition for all rows in SAS Programming

Populating value based on condition for all rows

smackerz1988 — Thu, 22 Sep 2022 10:09:11 GMT

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?

Re: Populating value based on condition for all rows

ChrisNZ — Thu, 22 Sep 2022 09:30:40 GMT

Can you add B1IND to your data set so we know what the expected output looks like?

Re: Populating value based on condition for all rows

smackerz1988 — Thu, 22 Sep 2022 10:10:48 GMT

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

Re: Populating value based on condition for all rows

ballardw — Thu, 22 Sep 2022 14:30:26 GMT

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.

Re: Populating value based on condition for all rows

smackerz1988 — Thu, 22 Sep 2022 15:00:29 GMT

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;