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

I would like to do the loop in proc sql function

LengYi — Mon, 12 Feb 2018 04:00:22 GMT

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

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

Reeza — Mon, 12 Feb 2018 04:04:04 GMT

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

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

LengYi — Mon, 12 Feb 2018 04:06:25 GMT

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)

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

art297 — Mon, 12 Feb 2018 04:53:36 GMT

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

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

kiranv_ — Mon, 12 Feb 2018 05:01:05 GMT

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

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

LengYi — Mon, 12 Feb 2018 05:03:09 GMT

Yes, this is what I want

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

kiranv_ — Mon, 12 Feb 2018 05:08:05 GMT

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

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

LengYi — Mon, 12 Feb 2018 05:09:57 GMT

Thanks I will try to use the code.

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

kiranv_ — Mon, 12 Feb 2018 05:15:18 GMT

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;

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

Astounding — Mon, 12 Feb 2018 14:11:13 GMT

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;