topic Re: merging rows in the table based on certain conditions in New SAS User

merging rows in the table based on certain conditions

LisaZ1 — Wed, 27 Jul 2022 22:15:16 GMT

Hi, I'm new to SAS and I'm trying to modify a dataset. basically, I want to add certain variables up if they have the same ID and the same mon and call it sum.

A is the input dataset and B is the output dataset that I want. I'm not sure if SAS is able to do this.

Any tips would be appreciated! Thank you!

data A;
input id $ mon $ cost $;
datalines;
111 01 11.34
112 02 3.56
112 02 4.98
112 03 5.88
114 04 7.89
;
run;
data B;
input id $ mon $ sum$;
datalines;
111 01 11.34
112 02 8.54
112 03 5.88
114 04 7.89
;
run;

Re: merging rows in the table based on certain conditions

ballardw — Thu, 28 Jul 2022 00:18:09 GMT

Just how are we supposed to 'sum' a character variable of COST???? If your variable is actually character then you likely need to fix that and make a numeric variable to allow arithmetic on it.

Assuming the $ for the input of cost is a typo (that makes it a character variable, If you want to display the value with dollar or other currency symbols you need to assign a format that does that).

One way:

data A;
input id $ mon $ cost ;
datalines;
111 01 11.34
112 02 3.56
112 02 4.98
112 03 5.88
114 04 7.89
;

proc summary data=a nway;
   by id mon;
   var cost;
   output out=want(drop=_type_ _freq_) sum= /autoname;
run;

I like summary for this as you can summarize many variables with many statistics and with the option of Autoname you don't even have to spend a lot of time thinking up new variable names. The option Autoname will place a suffix of the statistic after the variable names on the VAR statement.

BY group processing does require data to be sorted in the order of the variables on the statement but is a very powerful tool to process groups of records.

Re: merging rows in the table based on certain conditions

andreas_lds — Thu, 28 Jul 2022 06:20:34 GMT

There could be one problem in your data: the variable cost must be numeric to be used in calculations.

Re: merging rows in the table based on certain conditions

LisaZ1 — Thu, 28 Jul 2022 19:31:18 GMT

yeah, cost should be numeric variable. I didn't know $ means character, I thought $ is a must-have when you create data like this.
Thank you so much and how you did the proc summary did exactly what I need. I really appreciated that!

Re: merging rows in the table based on certain conditions

LisaZ1 — Thu, 28 Jul 2022 19:32:24 GMT

Yeah var cost should be a numeric instead of character. I didn't know that $ means character when creating datelines. Thank you!