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;
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.
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.
There could be one problem in your data: the variable cost must be numeric to be used in calculations.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.