topic Trying to create new tables through loops in SAS Procedures

Trying to create new tables through loops

leonh — Tue, 14 Feb 2012 20:15:21 GMT

I have a dataset which goes from 1990 to 2010; and for each year, there are few hundred observations. And I am trying to "partition" the dataset into different tables by year. I have the following, but I am pretty sure I can't do it, so what is the correct way to do it?

data work.test2;

do y=1989 to 2010;

set work.test;

if year(date)=y;

end;

run;

Thank you very much!

Trying to create new tables through loops

art297 — Tue, 14 Feb 2012 20:18:54 GMT

There are a number of ways to do what you want. My preference is to use a hash. Take a look at: http://communities.sas.com/message/102997#102997 , particularly Ksharp's offering

Trying to create new tables through loops

leonh — Tue, 14 Feb 2012 20:53:49 GMT

I've read the post, but I am new to SAS; would you be able to explain what the hash does?

data _null_ ;

dcl hash hh ( ) ;

hh.definekey ('k' ) ;

hh.definedata ('sex', 'name', 'age', 'height', 'weight') ;

hh.definedone () ;

do until(mod(k,5)=0 or last);

k+1;

set sashelp.class end=last ;

hh.add();

end;

gp+1;

hh.output(dataset: 'a'||strip(gp));

run;

in particularly dcl has hh, hh.define..., and mod.

Thank you!

Trying to create new tables through loops

art297 — Tue, 14 Feb 2012 21:00:18 GMT

First, I have a question for you. How many variables are in work.test and what are the variable names for each?

Trying to create new tables through loops

leonh — Tue, 14 Feb 2012 21:04:06 GMT

there are 11, and they are permno, hexcd, date, ret, prc, shrout, gvkey, linkdt, linkenddt, bkvlps. And it is by the variable "date" that I watn to break into into monthly parititions. The data spans from 1990 to 2010, so effectively into 252 smaller tables.

Thanks!

Leon

Re: Trying to create new tables through loops

Linlin — Tue, 14 Feb 2012 21:16:52 GMT

how about:

data have;

input date mmddyy10. v1-v3;

format date mmddyy10.;

cards;

01/02/1989 1 1 1

02/01/1990 2 2 2

01/03/2010 3 3 3

;

%macro test;

%do year=1989 %to 2010;

%do month=1 %to 12;

%let month=%sysfunc(putn(&month,z2.));

data test&year.&month;

set have;

if year(date)=&year and month(date)=&month then

output;

run;

%end; %end;

%mend;

%test

Re: Trying to create new tables through loops

art297 — Tue, 14 Feb 2012 21:35:17 GMT

Here is the code that will accomplish the task using the hash method:

data temp (index = (ym));

set test;

ym=put(date,monyy7.);

run;

data _null_ ;

dcl hash hh ( ) ;

hh.definekey ('k' ) ;

hh.definedata ('ym','permno','hexcd','date','ret','prc',

'shrout','gvkey','linkdt','linkenddt','bkvlps') ;

hh.definedone () ;

do k = 1 by 1 until ( last.ym ) ;

set temp;

by ym ;

hh.add () ;

end ;

hh.output(dataset: ym);

run;

The following paper gives a nice overview of the method: http://www2.sas.com/proceedings/sugi30/236-30.pdf

and the following tipsheet gives a brief overview of all of the terms one might use: http://support.sas.com/rnd/base/datastep/dot/hash-tip-sheet.pdf

Basically, the code is just creating an index of the values that you want to use to split the file, and then making use of your computer's memory to split the file based on the values stored in the index.

Re: Trying to create new tables through loops

leonh — Tue, 14 Feb 2012 21:53:53 GMT

Thanks! Both seems to work great! But a silly question (don't think it would be worth starting a new post), how would I create a variable that is equal to the maximum number of observations in the dataset (so actually this variable would be the same for every observation)?

Leon

Re: Trying to create new tables through loops

Linlin — Tue, 14 Feb 2012 22:53:07 GMT

example:

data class;

set sashelp.class end=last;

if last then call symputx('n',_n_);

max_obs=&n;

run;

proc print; title there are &n obs. in dataset &syslast;run;

Linlin

Re: Trying to create new tables through loops

art297 — Tue, 14 Feb 2012 22:55:33 GMT

If you are going to use Linlin's originally suggested method, you could get the number of observations in the same pass of the file if you replace the datastep with proc sql code. E.g.:

%macro test;

%do year=1989 %to 2010;

%do month=1 %to 12;

%let month=%sysfunc(putn(&month,z2.));

proc sql;

create table test&year.&month. as

select *, count(*) as nobs

from test

where year(date)=&year and

month(date)=&month

;

quit;

%end;

%mend;

%test

Re: Trying to create new tables through loops

Tom — Tue, 14 Feb 2012 23:24:31 GMT

Unless you are using a VIEW or other access type where SAS cannot calculate the number of observations then there is no need for the macro variable (unless you need it for the TITLE statement.

data class ;

set sashelp.class nobs=nobs;

max_obs=nobs;

run;

Note that you need to assign it to a new variable as the one named in the SET statement is automatically dropped.

Re: Trying to create new tables through loops

Linlin — Tue, 14 Feb 2012 23:38:05 GMT

Thank you Tom!

Re: Trying to create new tables through loops

icicleana — Wed, 23 Oct 2013 14:04:33 GMT

hello

can you give me an idea about macro coding for problem:

I have for instance 31 daily tables with same name in a way: xxxxx01092013,...., xxxxxx30092013. how can I make elegant solution in macro language? I would like to create one table from these 30 tables without simple append procedure.

Best and thanks

Re: Trying to create new tables through loops

Linlin — Wed, 23 Oct 2013 14:10:46 GMT

if you don't have other tables in the same library starting with XXXXX then you could do:

data one;

set XXXXX:;

run;

Re: Trying to create new tables through loops

art297 — Wed, 23 Oct 2013 14:10:54 GMT

I think you really ought to start a new discussion and, when you post it, provide more detail regarding what you have and what you want.

Re: Trying to create new tables through loops

icicleana — Thu, 24 Oct 2013 06:43:34 GMT

Hello Linlin, Arthur and everyone,

Linlin, thanks for the answer, but it is not so simple. I saw that you just created new one with setting older, but I have to manage with do list macro, and dates, such as:

let br=30

do %i=1 %to &br

%global datum

and something like put(dates.....???)

run;

%put &br,datum&i;

something like that

This is only part of loop.

I have 30 same tables ( same fields, but different values in it, name of one table is daily_product_01092013,...,daily_product_30092013) because in that tables we collect values from month to month and I would like to do append tables on cleverly way with macro coding ( I know to use EG and I now to use commands and tabs)

Best!