Conditional Processing Sum

[tmjeffer]

I am trying to create a new variable CF_TOTAL that is equal to Delivery '1' + Delivery '2'. See sample code below:

IF CAR = '4' AND FOOD = '2' THEN DELIVERY = '1'; 
ELSE IF CAR = '4' AND FOOD = '1' THEN DELIVERY = '2'; 
ELSE IF CAR IN ('1','2','3') AND FOOD = '1' THEN DELIVERY = '3'; 
ELSE IF CAR IN ('1','2','3') AND FOOD = '2' THEN DELIVERY = '4'; 
ELSE IF CAR = '9' OR FOOD = '9' THEN DELIVERY = '5'; 

CF_TOTAL = SUM(DELIVERY IN ('1','2'));

The problem that I am running into is the CF_TOTAL variable is being split into two groups (0,1) in my table (using proc tabulate) - (0) sum of all other 'Delivery' ; (1) sum of 'Delivery' 1 and 2. 

[Astounding]

You need to show an example (just 4 or 5 records) that illustrates the result you want.

[tmjeffer]

CF_Total should only be the sum of Delivery 1 and 2. 

[PaigeMiller]

Total of what? To get a total, you have to add some numbers, what numbers are you adding?

Can you show us a few records from your actual data set? And then show us what you want to add?

[tmjeffer]

I'm trying to create a variable, CF_Total, to sum Delivery 1 and Delivery 2 only. In the output, the CF_Total variable is split into two groups (0 and 1) and I'm not sure why that is happening either.  Please see attached.

[PaigeMiller]

How did you get the 166 under '1' for City '01J'? How do you get the 851 total for city '01J'?

Are there 851 from individual records for city '01J'? 

Twice, we have asked to see the input data here, you haven't shown that to us. I doubt we'll be able to give a code answer until we see a portion of the input data set you are working with. Please don't make us ask again.

[tmjeffer]

PaigeMiller, I do thank you for all of your help. There's really no need for the condescending tone when helping people. I'm manipulating variables in an existing data set (used a set statement). There's nothing else to see other than the PROC TABULATE. 

PROC TABULATE DATA = C_METHOD MISSING;
CLASS HOME DELIVERY CF_TOTAL ;
TABLE(HOME ALL),DELIVERY CF_TOTAL ALL *N*F=COMMA10./RTS=30 PRINTMISS MISSTEXT = "0";
KEYLABEL ALL= "Total"
N=' ' ;
LABEL
HOME = 'Cities'
DELIVERY = 'Method of Delivery';

RUN;

[Astounding]

As best I can tell (and there's still some guesswork going on here), the data is fine.  Just for clarity, I would recommend changing this statement:

CF_TOTAL = SUM(DELIVERY IN ('1','2'));

It should be simpler:

CF_TOTAL = DELIVERY IN ('1','2');

That way, it's clearer that CF_TOTAL is either 0 or 1 on every observation.  Then your TABULATE needs to change:

PROC TABULATE DATA = C_METHOD MISSING;
CLASS HOME DELIVERY;
VAR CF_TOTAL ;
TABLE(HOME ALL),
   (DELIVERY ALL) *N*F=COMMA10.
   CF_TOTAL * SUM =' '
 / RTS=30 PRINTMISS MISSTEXT = "0";
KEYLABEL ALL= "Total"
N=' ' ;
LABEL
HOME = 'Cities'
DELIVERY = 'Method of Delivery';

RUN;

See if this is closer to where you want to end up.

[PaigeMiller]

If you don't show us the data set you are working with, we can't really provide code that works.

[ballardw]

---

@tmjeffer wrote:

PaigeMiller, I do thank you for all of your help. There's really no need for the condescending tone when helping people. I'm manipulating variables in an existing data set (used a set statement). There's nothing else to see other than the PROC TABULATE. 

PROC TABULATE DATA = C_METHOD MISSING;
CLASS HOME DELIVERY CF_TOTAL ;
TABLE(HOME ALL),DELIVERY CF_TOTAL ALL *N*F=COMMA10./RTS=30 PRINTMISS MISSTEXT = "0";
KEYLABEL ALL= "Total"
N=' ' ;
LABEL
HOME = 'Cities'
DELIVERY = 'Method of Delivery';

RUN;

---

Considering that Proc Tabulate is one of the few procedures that can use multilabel formats a carefully defined multilabel format for the Delivery variable would likely give you what you want. But I can't be sure because you have not given any concrete input data to your proc tabulate. And I'm not going to spend any time trying to create fake data that might or might not have some resemblance to your actual data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[PaigeMiller]

---

@tmjeffer wrote:

I am trying to create a new variable CF_TOTAL that is equal to Delivery '1' + Delivery '2'. See sample code below:

IF CAR = '4' AND FOOD = '2' THEN DELIVERY = '1'; 
ELSE IF CAR = '4' AND FOOD = '1' THEN DELIVERY = '2'; 
ELSE IF CAR IN ('1','2','3') AND FOOD = '1' THEN DELIVERY = '3'; 
ELSE IF CAR IN ('1','2','3') AND FOOD = '2' THEN DELIVERY = '4'; 
ELSE IF CAR = '9' OR FOOD = '9' THEN DELIVERY = '5'; 

CF_TOTAL = SUM(DELIVERY IN ('1','2'));

---

So, to get the sums you want ... okay, I don't know what you are summing here. As written, you are summing the 0s and 1s created by DELIVERY IN ('1','2'), which gives you either a 0 or a 1 for each record in your data set. So please explain further what you want.

Side issue, there's no reason for delivery to be character, it's just extra typing, extra chance of typographical errors, make the variable DELIVERY numeric.