SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
LengYi
Calcite | Level 5

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

9 REPLIES 9
Reeza
Super User

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
Calcite | Level 5

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
Opal | Level 21

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_
Rhodochrosite | Level 12

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
Calcite | Level 5

Yes, this is what I want

kiranv_
Rhodochrosite | Level 12

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
Calcite | Level 5
Thanks I will try to use the code.
kiranv_
Rhodochrosite | Level 12

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
PROC Star

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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1823 views
  • 1 like
  • 5 in conversation