DATA Step, Macro, Functions and more

I would like to do the loop in proc sql function

Reply
Occasional Contributor
Posts: 15

I would like to do the loop in proc sql function

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

Super User
Posts: 24,012

Re: I would like to do the loop in proc sql function

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


 

Occasional Contributor
Posts: 15

Re: I would like to do the loop in proc sql function

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)

Super User
Posts: 8,217

Re: I would like to do the loop in proc sql function

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

 

PROC Star
Posts: 549

Re: I would like to do the loop in proc sql function

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

Occasional Contributor
Posts: 15

Re: I would like to do the loop in proc sql function

Yes, this is what I want

PROC Star
Posts: 549

Re: I would like to do the loop in proc sql function

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 

Occasional Contributor
Posts: 15

Re: I would like to do the loop in proc sql function

Thanks I will try to use the code.
PROC Star
Posts: 549

Re: I would like to do the loop in proc sql function

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;

Super User
Posts: 6,934

Re: I would like to do the loop in proc sql function

[ Edited ]

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;

Ask a Question
Discussion stats
  • 9 replies
  • 225 views
  • 1 like
  • 5 in conversation