DATA Step, Macro, Functions and more

Using Arrays to sum obs like proc sql sum function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Using Arrays to sum obs like proc sql sum function

Hello I am trying to sum a large amount of obs called NAICS4 which have 390 distinct values. The taxable??? colume has 90 distinct columes and will increase monthly. I can do a sum statement in proc sql like this:

proc sql; create table state4SortSum as select

naics4, sum(taxable190) as sumTaxable190, sum(taxable191) as sumTaxable191, sum(taxable390) as sumTaxable390

from rg.state4sort

group by naics4; quit;

But I am not wanting to write 90+ sum statements, so I tried to write a array:

data stateTest; length item $4;

  array txSum{*} taxable190-taxable211 ;

do i = 1 to dim(txSum);

  set rg.state4sort;

  by naics4;

  item =naics4;

  tx = sum( of txSum{i})  ;

  if last.naics4 then output;

end;

drop tx;

run;

However this array is only giving me the value of the last obs it runs through for each NAICS4 and I want it to give me the sum of all like it would in SQL. Any help with a better way to get the equivialent of the SQL without writing out everything would be great.  

 

naics4taxable190taxable290taxable390taxable490taxable191taxable291taxable391taxable491taxable192taxable292taxable392taxable492
1111-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001
1111
1111
1112-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001
1112-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001
1112
1112
1113233981529621359647862367315961174531594392764824140-0.001236359
1113-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001
1113-0.001-0.001-0.001
1113-0.001-0.001-0.001
1113-0.001-0.001131411-0.001-0.001-0.0014772
1113
1113-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001-0.001

Accepted Solutions
Solution
‎02-03-2012 01:18 PM
Super User
Super User
Posts: 6,497

Re: Using Arrays to sum obs like proc sql sum function

The ARRAY statement is to allow you to work across mulitple variables in the same observation.

The SUM() function in PROC SQL is to allow you to work across multiple obsevations.

You want to use PROC SUMMARY to sum your variables.

proc summary data=rg.state4sort nway ;

  class naics4;

  var Taxable: ;

  output out=state4SortSum sum=;

run;

You can work out how to rename the variables from Taxable ... to SumTaxable...

View solution in original post


All Replies
Solution
‎02-03-2012 01:18 PM
Super User
Super User
Posts: 6,497

Re: Using Arrays to sum obs like proc sql sum function

The ARRAY statement is to allow you to work across mulitple variables in the same observation.

The SUM() function in PROC SQL is to allow you to work across multiple obsevations.

You want to use PROC SUMMARY to sum your variables.

proc summary data=rg.state4sort nway ;

  class naics4;

  var Taxable: ;

  output out=state4SortSum sum=;

run;

You can work out how to rename the variables from Taxable ... to SumTaxable...

Super Contributor
Posts: 1,636

Re: Using Arrays to sum obs like proc sql sum function

anothe approach:

example:

data have;

input naics4 $ taxable1-taxable5;

cards;

1 20 30 40 50 60

1 25 35 45 55 65

1 30 40 50 60 70

2 20 30 40 50 60

2 25 35 45 55 65

3 30 40 50 60 70

;

run;

proc sql noprint;

select catt('sum','(',name,')',' as ',catt(' sum',name))into : names separated by ','

  from dictionary.columns

    where libname='WORK' and memname='HAVE' and upcase(name) like 'TAXABLE%' ;

quit;

%put &names;

proc sql;

  create table want as select naics4,&names

    from have

       group by naics4;

quit;

Linlin

Respected Advisor
Posts: 3,124

Using Arrays to sum obs like proc sql sum function

An array approach, borrowed examples from LinLin's post:

data have;

input naics4 $ taxable1-taxable5;

cards;

1 20 30 40 50 60

1 25 35 45 55 65

1 30 40 50 60 70

2 20 30 40 50 60

2 25 35 45 55 65

3 30 40 50 60 70

;

run;

data want (drop=taxSmiley Happy;

do until (last.naics4);

set have;

by naics4;

array tax(*) taxable1-taxable5;

array st(*) sum_tax1-sum_tax5;

do _n_=1 to dim(tax);

   st(_n_)+tax(_n_);

end;

end;

output;

call missing (of _all_);

run;

proc print;run;

Regards,

Haikuo

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 1427 views
  • 7 likes
  • 4 in conversation