topic Re: For each individual, counting how many variable have a specific value in SAS Programming

For each individual, counting how many variable have a specific value

Emma_at_SAS — Sun, 13 Dec 2020 22:12:32 GMT

I have a hospital data set with a list of 18 variables showing whether each patient was admitted to the hospital in the last 18 years (1=yes, 2=no) (I have some other variables that I do not want to change). In a new variable (number_of_admissions_to_hospital) I want to count how many times each patient was admitted to the hospital in the last 18 years.

ID Y_2000 Y_2001 Y_2002 .... Y_2018 number_of_admissions_to_hospital

1 1 1 2 2 2

2 2 1 2 2 1

3 1 1 2 1 3

I appreciate it if you help me to add number_of_admissions_to_hospital to the variables of my data set.

Thank you!

Re: For each individual, counting how many variable have a specific value

Kurt_Bremser — Sun, 13 Dec 2020 22:54:56 GMT

proc transpose data=have out=long (drop=_name_);
by id;
var y_:;
run;

proc sql;
create table want as
  select
    id,
    sum(case
      when col1 = 1
      then 1
      else 0
    end) as number_of_admissions_to_hospital
  from long
  group by id
;
quit;

As usual, a long dataset layout makes the coding simple.

Re: For each individual, counting how many variable have a specific value

Emma_at_SAS — Sun, 13 Dec 2020 23:17:56 GMT

Thank you, KurtBremser. So, with other variables in my data set, how does the long version of the data works? Then I need to return to the wide version of data. This new variable will be just a covariate that I will use in regression analysis and I need to work with the wide format of the data.

I found ways to concatenate a list of character or numeric variables. I wonder if there is a function that I use to calculate the sum of values for these years where my Year values are 1 (that is individuals were admitted to hospital).

Thank you!

Re: For each individual, counting how many variable have a specific value

PaigeMiller — Mon, 14 Dec 2020 00:24:49 GMT

In my opinion, covariates in regression analysis (or any modeling) are the exception to the rule where you should prefer long data sets to wide. I would still create the data set long, as Kurt Bremser has done, do as much analysis as you can for using the long data set, and then for regression use PROC TRANSPOSE to return it to wide.

Re: For each individual, counting how many variable have a specific value

Emma_at_SAS — Mon, 14 Dec 2020 04:53:49 GMT

Excellent! Thank you KurtBremser and PaigeMiller. Both your comments helped me to count the number of hospital admissions. How may I do similar steps for 2 sets of variables and create 2 new variables. I have another set of 7 variables for different infections and I want to count in total, how many infections each individual had. This is only for the last year's data (7 observations for each individual for the last year).

I tried adding a second set of variables in the PROC TRANSPOSE step, and I saw the variables in the long format but I could not make the loop to count 1's in those 7 variables.
proc transpose data=have out=long (drop=_name_);
by id;
var y_:;
var infection_:;
run;

Thank you!

Re: For each individual, counting how many variable have a specific value

Kurt_Bremser — Tue, 15 Dec 2020 13:23:20 GMT

proc sql;
create table want as
  select
    id,
    sum(case
      when col1 = 1 and upcase(_name_) like '%Y'
      then 1
      else 0
    end) as number_of_admissions_to_hospital
    sum(case
      when col1 = 1 and upcase(_name_) like '%INF'
      then 1
      else 0
    end) as number_of_infections
  from long
  group by id
;
quit;

Add additional conditions as needed to the CASE clauses.

Re: For each individual, counting how many variable have a specific value

Emma_at_SAS — Tue, 15 Dec 2020 14:22:36 GMT

Thank you very much, KurtBremser!

Re: For each individual, counting how many variable have a specific value

Emma_at_SAS — Sun, 20 Dec 2020 22:43:21 GMT

Thank you, KurtBremser. I cannot figure out what does the following part does. May you please help me with that?

and upcase(_name_) like '%Y'

Thank you!

Re: For each individual, counting how many variable have a specific value

Kurt_Bremser — Mon, 21 Dec 2020 08:17:50 GMT

_NAME_ is a variable created by the TRANSPOSE procedure, it holds the name of the variable that was transposed to this particular observation.

Here, I select for the values of all entries where the former variable name started with a Y, either upoer or lower case.