topic Re: how to output where a "Yes" exists in many rows in SAS Programming

how to output where a "Yes" exists in many rows

GS2 — Thu, 30 Apr 2020 13:25:52 GMT

Using SAS 9.4

I have the following data:

acl_recon

acl_repair

pcl_recon

pcl_repair

mcl_recon

mcl_repair

lcl_recon

lcl_repair

plc_recon

plc_repair

All of the above are binary (Yes/No) variables and none have missing data. Is there an efficient way to code looking for where a "Yes" occurs within these variables and outputting where multiple "Yes" occur? Meaning, If "acl_recon" and "lcl_recon" and "mcl_repair" and "plc_recon" are all = 'Yes' the output variable (I would like all of the data to go into 1 variable) should read "ACL recon/LCL recon/MCL repair/PLC recon". It is possible there could only be 1 variable with a "Yes" or there could be 9 variables with a "Yes". Any thoughts would be helpful. Thank you

Is it possible to do this without having to write lots of if/then statements? Thank you

Re: how to output where a "Yes" exists in many rows

PeterClemmensen — Thu, 30 Apr 2020 13:34:51 GMT

If I understand you correctly, do this

data have;
input (acl_recon acl_repair pcl_recon pcl_repair mcl_recon mcl_repair lcl_recon lcl_repair plc_recon plc_repair)(:$);
datalines;
Yes No Yes Yes No Yes No Yes Yes Yes
Yes No Yes No No Yes No Yes Yes Yes
Yes No No Yes No Yes No No Yes Yes
;

data want(drop=i);
    set have;
    length newvar $ 200;
    array a _character_;
    do i = 1 to dim(a);
        if a[i] = 'Yes' then do;
            newvar = catx('/', newvar, vname(a[i]));
        end;
    end;
run;

Re: how to output where a "Yes" exists in many rows

GS2 — Thu, 30 Apr 2020 14:06:17 GMT

Thank you! That worked perfectly

Re: how to output where a "Yes" exists in many rows

PeterClemmensen — Thu, 30 Apr 2020 14:08:22 GMT

Anytime 🙂

Re: how to output where a "Yes" exists in many rows

ballardw — Thu, 30 Apr 2020 14:32:49 GMT

Just for giggles, what is that variable with variable names to be used for?

And with what tools?

Re: how to output where a "Yes" exists in many rows

GS2 — Thu, 30 Apr 2020 15:10:18 GMT

It is being used for a descriptive gut check basically

Re: how to output where a "Yes" exists in many rows

Tom — Thu, 30 Apr 2020 16:30:19 GMT

If your flags were actual binary numeric variables you could use SUM() function.

proc print data=have;
where 1<sum(acl_recon,acl_repair,pcl_recon,pcl_repair,mcl_recon,mcl_repair 
           ,lcl_recon,cl_repair,plc_recon,plc_repair)
;
run;

With character variables you can use COUNT() and CATS().

where 1<count(cats(acl_recon,acl_repair,pcl_recon,pcl_repair,mcl_recon,mcl_repair 
                  ,lcl_recon,cl_repair,plc_recon,plc_repair)
             ,'Yes')
;