topic Re: counting data across column in SAS Programming

counting data across column

CathyVI — Wed, 20 Mar 2024 19:54:57 GMT

Hello,

Hope someone can help me out. I have a dataset where someone can complete 2 or 3 forms (e.g., form A, form B & form C).
I want a count of someone in Form A only, Form B only and Form C only and those who filled more than 1 form (mult_form). If they complete form A and form B, they should be counted as mult_form. For example, ID 102 & 104 to be counted as multi_form. Here is my dummy data.

data dummy;
input id$ 1-3 FormA FormB FormC ;
infile datalines ;
datalines;
101 1 0 . 
101 . 0 . 
102 1 0 . 
102 . 1 . 
103 . . 0 
103 . . 1 
104 1 0 . 
104 . . 1
105 . 1 . 
;
;
run;

Thanks in advance.

Re: counting data across column

ballardw — Wed, 20 Mar 2024 17:25:43 GMT

I am confused that you even mention 25 or 45 as don't see anything actually related to those. Please use VARIABLE names and shown values to discuss rules. Otherwise we have to interpret (translate: make a lot of guesses) what we think you mean. For example, I do not understand what "Form A only" means. You show variables with some of that text in the name but do not really tell use how to identify a "Form A Only" record, id, group or what have.

And please provide an example of two of pointing which variables, values on which rows are used to satisfy some of these criteria.

I can't tell what you are starting with. Or really what the result should look like. Especially since none of the values of FormType are "Form C" but you show results in "FormC30_plus".

Re: counting data across column

Patrick — Thu, 21 Mar 2024 06:31:08 GMT

The following code will set variable MultiForm to 1 if for an ID more than one Form variable got a value of 1.

data have;
  input id$ 1-3 FormA FormB FormC;
  infile datalines;
  datalines;
101 1 . . 
101 1 0 . 
102 1 0 . 
102 . 1 . 
103 . . 0 
103 . . 1 
104 1 0 . 
104 . . 1
105 . 1 . 
;
run;

proc sql;
/*  create table want as*/
  select i.*,sum(FormA,FormB,FormC)>1 as MultiForm
  from
  (
    select 
      id,
      max(FormA) as FormA,
      max(FormB) as FormB,
      max(FormC) as FormC
    from have
    group by id
  ) i
  ;
quit;

Re: counting data across column

CathyVI — Thu, 21 Mar 2024 13:39:45 GMT

@Patrick @ballardw based on your response, if I want to have a count of formA only, formB only, formC only and multi_form how will i get the frequency count?

I guess what am saying is how would I count formAonly without counting (where 102=1) as part of formA.

Please can I learn this using data step? Thanks

Re: counting data across column

Patrick — Thu, 21 Mar 2024 23:20:24 GMT

@CathyVI Below one way when using SQL

proc sql;
/*  create table want as*/
  select 
    *,
    ifn(MultiForm=1 and FormA=1,0,FormA) as FormA_only,
    ifn(MultiForm=1 and FormB=1,0,FormB) as FormB_only,
    ifn(MultiForm=1 and FormC=1,0,FormC) as FormC_only
  from
  (
    select 
      *,
      sum(FormA,FormB,FormC)>1 as MultiForm
    from
    (
      select 
        id,
        max(FormA) as FormA,
        max(FormB) as FormB,
        max(FormC) as FormC
      from have
      group by id
    ) 
  )
  ;
quit;

Re: counting data across column

Mazi — Fri, 22 Mar 2024 08:06:27 GMT

data dummy;
input id$ 1-3 FormA FormB FormC ;
infile datalines ;
datalines;
101 1 0 . 
101 . 0 . 
102 1 0 . 
102 . 1 . 
103 . . 0 
103 . . 1 
104 1 0 . 
104 . . 1
105 . 1 . 
;
;
run;

data want;
	do until(last.id);
		set dummy;
		by id;
		if formA then _forma=1;
		if formB then _formb=1;
		if formC then _formc=1;
	end;
	if sum(of _form:)>1 then multiForm=1;
	else multiForm=0;
	if multiForm=0 then do;
		if _forma then formAOnly=1;
		else if _formB then formBOnly=1;
		else if _formC then formCOnly=1;
	end;
	output;
	drop _:;
run;

Can you try this?