I would like to do the loop in proc sql function

[LengYi]

Hi everyone,

I would like to do the loop the proc sql; function.

Currently, I have a code look like

proc sql;

create table temp1 as select count(*) as N,

sum(T1) as sum_T1,

sum(T2) as sum_T2,

sum(T3) as sum_T3,

.,

.,

.,

sum(T100) as sum_T100

from temp

group by year;

quit;

Are there any shorter way to code like this.

Thanks

[Reeza]

Not using SQL, because it doesn't support variable lists. 

You can however use PROC MEANS to do this easily. 

proc means data=temp nway stackods;
by year;
var T1-T100;
output out=want sum= /autoname;
run;

---

@LengYi wrote:

Hi everyone,

I would like to do the loop the proc sql; function.

Currently, I have a code look like

proc sql;

create table temp1 as select count(*) as N,

sum(T1) as sum_T1,

sum(T2) as sum_T2,

sum(T3) as sum_T3,

.,

.,

.,

sum(T100) as sum_T100

from temp

group by year;

quit;

Are there any shorter way to code like this.

Thanks

---

[LengYi]

Thanks for a quick reply,

What if I would like to have 'N' in the table how can I deliver the N (Total number of datalines)

[art297]

Just add n= to the output statement. i.e.:

proc means data=temp nway stackods;
  by year;
  var T1-T100;
  output out=want sum= n= /autoname;
run;

Art, CEO, AnalystFinder.com

[kiranv_]

I think OP wants overall count per year, but this code will count for all T1-T100, which will be basically same but will repeated 100 times like T1_N - T100_N, generally all he wants one column of count per year

[LengYi]

Yes, this is what I want

[kiranv_]

One way I think of is;

Use the proc means code and then do a 

proc sql;

create temp table as 

select year, count(*) as tot

from demo

group by year;

data final;

merge meanstable temp ;

by year;

run;

or drop columns in the earlier code 

[LengYi]

Thanks I will try to use the code.

[kiranv_]

or just this to @art297 code

proc means data=temp nway stackods;
by year;
var T1-T100;
output out=want(drop = T2_N--T100_N rename=T1_N = Count) sum= n= /autoname;
run;

[Astounding]

In the original SQL code, N is a count of number of observations.  It's value does not change if one of the incoming variables has a missing value on some observations.  Therefore, that count is already part of the output data set from PROC MEANS, contained in the variable _FREQ_.  There is no need to calculate the N statistic ... and in fact, it can give a different answer depending on the number of missing values.  Here's a variation on the program that applies this idea:

proc summary data=have nway;

class year;

var t1-t100;

output out=want (drop=_type_ rename=(_freq_=n t1-t100=sum_t1-sum_t100)) sum=;

run;