Hi,
I have >5.000 rows and 130 columns.
Column 1: Postal codes (inkl.duplicates)
Column 2-129 with €-values (names of columns: sp1-sp129)
What I need is a new row with the result of the sum of all rows for each numeric column (sp1-sp129), if the postal code is XXXXX. How can I do this with a short sql-code? Thank you for help!
For example:
postal_code | sp1 | sp2 | spN |
11111 | 65 | 78 | ... |
11111 | 12 | 65 | ... |
22222 | 87 | 21 | ... |
33333 | 42 | 45 | ... |
22222 | 65 | 87 | ... |
11111 | 24 | 86 | ... |
1111_sum | 101 | 229 | ... |
I wouldn't even attempt this in PROC SQL
proc summary data=have nway;
class postal_code;
var sp1-sp129;
output out=_sums_ sum=;
run;
proc sort data=have;
by postal_code;
run;
data want;
length postal_code $ 10;
set have _sums_;
by postal_code;
if _type_=1 then postal_code=cats(postal_code,'_sum');
run;
I wouldn't even attempt this in PROC SQL
proc summary data=have nway;
class postal_code;
var sp1-sp129;
output out=_sums_ sum=;
run;
proc sort data=have;
by postal_code;
run;
data want;
length postal_code $ 10;
set have _sums_;
by postal_code;
if _type_=1 then postal_code=cats(postal_code,'_sum');
run;
double-post: I got it! I just put the Where-command into the summary-command, built a table per sum-group and combined all tables afterwards.
Thanks!
How do you include duplicates with the different values but then sum them up if the postal code is XXX? Those are contradictory requirements.
Do you mean you only want postal codes with the same characters added up, ie with the pattern of letters and just the total at the bottom? Do you expect to see a line of 22222_sum as well?
@Konkordanz wrote:
Hi,
I have >5.000 rows and 130 columns.
Column 1: Postal codes (inkl.duplicates)
Column 2-129 with €-values (names of columns: sp1-sp129)
What I need is a new row with the result of the sum of all rows for each numeric column (sp1-sp129), if the postal code is XXXXX. How can I do this with a short sql-code? Thank you for help!
For example:
postal_code sp1 sp2 spN 11111 65 78 ... 11111 12 65 ... 22222 87 21 ... 33333 42 45 ... 22222 65 87 ... 11111 24 86 ... 1111_sum 101 229 ...
It's often useful to show the desired result based on the sample data (ideally provided via a SAS data step). Below 3 want options for what you might ask for. Check if any of the options fits your requirement or else let us know what comes closest and what's still missing.
As for a total line: That's then more about creating a report than a new table. So if you need a report then please tell us - and SHOW us how this report would need to look like.
data have;
input postal_code $ sp1 sp2;
datalines;
11111 65 78
11111 12 65
22222 87 21
33333 42 45
22222 65 87
11111 24 86
;
data want_1;
set have;
spN=sum(of sp:);
run;
data want_2;
set have;
if postal_code='11111' then
spN=sum(of sp:);
run;
data want_3;
set have;
if postal_code='11111';
spN=sum(of sp:);
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.