BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
LisaZ1
Obsidian | Level 7

 

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

View solution in original post

4 REPLIES 4
ballardw
Super User

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.

 

LisaZ1
Obsidian | Level 7
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!
andreas_lds
Jade | Level 19

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

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 4 replies
  • 671 views
  • 0 likes
  • 3 in conversation