topic How to create a sum variable and add it to data set in SAS Programming

How to create a sum variable and add it to data set

rauster — Sat, 19 Jan 2019 13:25:36 GMT

Hello, first time poster here, been learning SAS over the last few weeks for some research on national health databases. Each row of my data set corresponds to a patient, and there are ~200 columns with lots of different variables about the patient. 15 of these columns correspond to procedures that a patient has undergone, and are named PR1, PR2 ... PR15. The procedures are coded using standardized numbers used in medical coding/billing. For example, 3611 is a heart bypass. I need to create a new variable for each patient that is a sum of all the times a specific procedure is performed. So I need to somehow create a count of the number of times 3611 appears in the patient's 15 procedure columns. I then need to add this result as a new variable to the patient. I appreciate your help!

Re: How to create a sum variable and add it to data set

Astounding — Sat, 19 Jan 2019 14:03:24 GMT

To get started, you need to know whether your PR1-PR15 variables are character or numeric. If you don't know, PROC CONTENTS will tell you.

Since each row corresponds to a patient, the problem is relatively easy. Here's the beginner's way:

data want;

set have;

count_3611=0;

if PR1=3611 then count_3611 + 1;

if PR2=3611 then count_3611 + 1;

...

if PR15=3611 then count_3611 + 1;

run;

Note that this assumes that your variables are numeric. If they are actually character, you need to add quotes to the comparisons:

if PR1="3611" then count_3611 + 1;

A more advanced method would use arrays to cut down on the amount of typing needed. This might be something you learn maybe 6 months into learning SAS:

data want;

set have;

array pr {15} pr1-pr15;

count_3611 + 1;

do k=1 to 15;

if pr{k}=3611 then count_3611 + 1;

end;

drop k;

run;

Re: How to create a sum variable and add it to data set

Reeza — Sat, 19 Jan 2019 18:41:53 GMT

@rauster wrote:

Hello, first time poster here, been learning SAS over the last few weeks for some research on national health databases. Each row of my data set corresponds to a patient, and there are ~200 columns with lots of different variables about the patient. 15 of these columns correspond to procedures that a patient has undergone, and are named PR1, PR2 ... PR15. The procedures are coded using standardized numbers used in medical coding/billing. For example, 3611 is a heart bypass. I need to create a new variable for each patient that is a sum of all the times a specific procedure is performed. So I need to somehow create a count of the number of times 3611 appears in the patient's 15 procedure columns. I then need to add this result as a new variable to the patient. I appreciate your help!

You can use WHICHC or WHICHN.

Assuming your data is sorted by Patient:

data want;
set have;

*do this per patient;
by patientID; 

*if first record of a patient, set count to 0;
if first.patient then count=0;

*if any pr1-pr15 = 3611 then increment count;
if whichc('3611', of pr1-pr15) then count+1;

*keep final count per patient;
if last.patientID then output;


run;

This will only tell you how many per patient, you'll need to merge it back with the main data set.

Re: How to create a sum variable and add it to data set

rauster — Sat, 19 Jan 2019 21:05:37 GMT

This worked perfectly, thanks a lot for your help.