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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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