Help using Base SAS procedures

Trying to create new tables through loops

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Trying to create new tables through loops

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!


Accepted Solutions
Solution
‎02-14-2012 04:16 PM
Super Contributor
Posts: 1,636

Re: Trying to create new tables through loops

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

View solution in original post


All Replies
PROC Star
Posts: 7,489

Trying to create new tables through loops

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

Occasional Contributor
Posts: 6

Trying to create new tables through loops

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!

New Contributor
Posts: 2

Re: Trying to create new tables through loops

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

Super Contributor
Posts: 1,636

Re: Trying to create new tables through loops

Posted in reply to icicleana

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

data one;

set XXXXX:;

run;

New Contributor
Posts: 2

Re: Trying to create new tables through loops

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 Smiley Happy

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!

PROC Star
Posts: 7,489

Re: Trying to create new tables through loops

Posted in reply to icicleana

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.

PROC Star
Posts: 7,489

Trying to create new tables through loops

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

Occasional Contributor
Posts: 6

Trying to create new tables through loops

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

Solution
‎02-14-2012 04:16 PM
Super Contributor
Posts: 1,636

Re: Trying to create new tables through loops

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

PROC Star
Posts: 7,489

Re: Trying to create new tables through loops

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.

Occasional Contributor
Posts: 6

Re: Trying to create new tables through loops

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

Super Contributor
Posts: 1,636

Re: Trying to create new tables through loops

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

Super User
Super User
Posts: 7,074

Re: Trying to create new tables through loops

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.

Super Contributor
Posts: 1,636

Re: Trying to create new tables through loops

Thank you Tom!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 2369 views
  • 7 likes
  • 5 in conversation