- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes, this is what I want
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;