topic Re: how to sum a column per variable that changes in SAS Programming

how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 04:35:24 GMT

I am quite a novice in programming and I kind need your help regarding an issue I notice.

I have a table:

date  ID  secondary  D  E
date1 b1   c1          d1 e1
date1 b    c           d  e
date3 b2   c1          d3 e3
date4 b2   c2          d4 e4

Id and secondary should be unique and always matching. But I notice that they are not. It's either correct the ID or the secondary ID. I want to sum all the D column per unique ID. Thanks you all for reading and if you have any inputs would be very helpful

Re: how to sum a column per variable that changes

Shmuel — Fri, 30 Sep 2022 05:39:16 GMT

The statemnt "BY ID secondary" is unique if the combination of the by variables is unique.

In your example: 'b1 c1', 'b2 c1', 'b2 c2' - these are all unique values.

In order to sum D column it should be numeric otherwise it has no sense;

Re: how to sum a column per variable that changes

PeterClemmensen — Fri, 30 Sep 2022 05:19:05 GMT

Here's how I interpret your question: In the first row, you want Id to equal Secondary. So you change either Id to 'c1' or Secondary to 'b1'. Then you do that for all obs, so that if ID = Secondary = 'b1' for example, then no other obs can have ID = 'b1' or Secondary = 'B1', correct?

I don't understand the summation of the D Columns though. Help me here?

Re: how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 06:34:02 GMT

YESSS 🙂 ... correct thank you. And then I want to sum column d for each (unique) ID . Thank you PeterC

Re: how to sum a column per variable that changes

PeterClemmensen — Fri, 30 Sep 2022 06:39:02 GMT

But D is a character column?

Re: how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 06:53:44 GMT

no everything is numeric even ID. It's like this. As you can see we have different dates (but for the same date multiple customers). I notice that customer 258 for secondary ID 0004 during the years the ID or the secondary ID changes. And I wan to assign the same ID as the frist date and the same secondary ID as the first date ( or any day just to be cnsistent). I want to to do this cause I want to know how many expenses each customer has during the years. There are like 50m obs.

date,             ID,        secondary ID,     expenses
jul2020           258           0004               1000
jul2020           xxx           xxxx                 xxx
......              ....               ....                 .....
......              ....               ....                   .....
aug2020         258             0008               2000
aug2020         xxx             xxxx                xxx
aug2020         500              0004             1000

Re: how to sum a column per variable that changes

Shmuel — Fri, 30 Sep 2022 07:35:21 GMT

If I understand you correctly you want to summarize the expences per user=ID.

That can be done either by proc summary (or peoc means, same syntax):

proc summary data=have noprint;
  calss ID;   /* or ID secondary ? */
  var expenses;
  output out=want sum=;
run;

or by sql

proc sql;
   select id, 
        sum(expenses)
   from have
   group by ID;   /* or id, secodary */
quit;

Re: how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 07:37:56 GMT

yeah but if I do that and don't take into account that the ID for the same customer is changing for some months wouldn't I loose some sums?

Re: how to sum a column per variable that changes

Shmuel — Fri, 30 Sep 2022 08:29:07 GMT

To summarize per ID per month you need to add the date with appropriate format to the class or by statemnet.

Using proc summary it could be: by ID date; format date yymmn6.;

Re: how to sum a column per variable that changes

andreas_lds — Fri, 30 Sep 2022 10:41:04 GMT

@neo_noone wrote:

I am quite a novice in programming and I kind need your help regarding an issue I notice.

I have a table:
date  ID  secondary  D  E
date1 b1   c1          d1 e1
date1 b    c           d  e
date3 b2   c1          d3 e3
date4 b2   c2          d4 e4
Id and secondary should be unique and always matching. But I notice that they are not. It's either correct the ID or the secondary ID. I want to sum all the D column per unique ID. Thanks you all for reading and if you have any inputs would be very helpful

The table has zero numeric variables (except for date, maybe), so there seems to be nothing that can be summed. Please post the data you have in usable form (a data step using datalines) and show the expected output for that dataset.

Re: how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 11:14:56 GMT

no everything is numeric even ID. It's like this. As you can see we have different dates (but for the same date multiple customers). I notice that customer 258 for secondary ID 0004 during the years the ID or the secondary ID changes. And I wan to assign the same ID as the first date and the same secondary ID as the first date ( or any day just to be consistent). I want to to do this cause I want to know how many expenses each customer has during the years. There are like 50m obs.

date,             ID,        secondary ID,     expenses
jul2020           258          0004               1000
jul2020           xxx           xxx               xxx
…        ….               ….              …..
aug2020         258             0008               2000
aug2020         xxx             xxx               xxx
aug2020         500             0004             1000

Re: how to sum a column per variable that changes

PaigeMiller — Fri, 30 Sep 2022 11:46:50 GMT

@neo_noone wrote:

no everything is numeric even ID. It's like this. As you can see we have different dates (but for the same date multiple customers). I notice that customer 258 for secondary ID 0004 during the years the ID or the secondary ID changes. And I wan to assign the same ID as the first date and the same secondary ID as the first date ( or any day just to be consistent). I want to to do this cause I want to know how many expenses each customer has during the years. There are like 50m obs.
date,             ID,        secondary ID,     expenses
jul2020           258          0004               1000
jul2020           xxx           xxx               xxx
…        ….               ….              …..
aug2020         258             0008               2000
aug2020         xxx             xxx               xxx
aug2020         500             0004             1000

Please fix this table, so that there are no xxx, there are actual numbers in the columns (even if you have to make up some numbers), and then show us the resulting table you want. Actually do the math for this small table, sum the columns and show us the desired output.

Re: how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 11:55:23 GMT

thank you for trying

lets say this is the original table

date	first_id	second_id	expenses
jul2020	400	0101	1000
jul2020	500	89	2001
aug2020	400	0202	1500
aug2020	500	89	2001
sep2020	402	0101	3200
sep2020	500	89	2001
oct2020	403	0101	200
oct2020	500	89	2001
nov2020	400	0202	100
nov2020	500	89	2001

If you notice customer 400 has second ID 0101. But during the years the first ID or the second ID changes (not both at the same time). I want the table to become something like that

date	first_id	second_id	expenses
jul2020	400	0101	1000
jul2020	500	89	2001
aug2020	400	0101	1500
aug2020	500	89	2001
sep2020	400	0101	3200
sep2020	400	89	2001
oct2020	400	0101	200
oct2020	500	89	2001
nov2020	400	0101	100
nov2020	500	89	2001

now the customer with first ID 400 and second 0101 keeps it's original values thought the year.

After that I want to aggregate the expenses it by first ID ( I can do that) I just can't change the first ID or the second ID

Re: how to sum a column per variable that changes

PaigeMiller — Fri, 30 Sep 2022 11:58:55 GMT

So the step is to change 0202 under SECOND_ID to 0101? That's the only change? Or is it more general than that where sometimes 0404 change to 0101 as well? Or 3333 changes to 0237?

Re: how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 12:12:27 GMT

YESSS and if the first ID is different than the original ( if it is, then the second ID will be the same as the original ID if you notice) change it to the original first ID

Re: how to sum a column per variable that changes

PaigeMiller — Fri, 30 Sep 2022 12:17:33 GMT

Okay what happened here? There was a response from you that showed a table in which 0202 was changed to 0101, and now it is gone. Did you do that or did the SAS Communities do that?

Re: how to sum a column per variable that changes

neo_noone — Fri, 30 Sep 2022 12:28:59 GMT

Nope wasn't me. let me just update it

original

date	first_id	second_id	expenses
jul2020	400	0101	1000
jul2020	500	89	2001
aug2020	400	0202	1500
aug2020	500	89	2001
sep2020	402	0101	3200
sep2020	500	89	2001
oct2020	403	101	200
oct2020	500	89	2001
nov2020	400	202	100
nov2020	500	89	2001

the target table

date	first_id	second_id	expenses
jul2020	400	0101	1000
jul2020	500	89	2001
aug2020	400	0101	1500
aug2020	500	89	2001
sep2020	400	0101	3200
sep2020	400	89	2001
oct2020	400	0101	200
oct2020	500	89	2001
nov2020	400	0101	100
nov2020	500	89	2001

Re: how to sum a column per variable that changes

PaigeMiller — Fri, 30 Sep 2022 12:37:39 GMT

how about this:

data have; 
input date :monyy7.	first_id $	second_id $	expenses;
format date monyy7.;
cards;
jul2020	400	0101 1000
jul2020	500	89 2001
aug2020	400	0202 1500
aug2020	500	89 2001
sep2020	402	0101 3200
sep2020	500	89 2001
oct2020	403	0101 200
oct2020	500	89 2001
nov2020	400	0202 100
nov2020	500	89 2001
;
proc sort data=have;
    by first_id date;
run;
data want;
    set have;
    by first_id;
    retain second_id1;
    if first.first_id then second_id1=second_id;
    drop second_id;
run;
proc sort data=want;
    by date first_id;
run;

although I'm still not sure about your question about sums, what sums do you want from this table? Please show me. (Also, why does second_id even matter?)