BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leonh
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

15 REPLIES 15
art297
Opal | Level 21

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

leonh
Calcite | Level 5

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!

icicleana
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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

data one;

set XXXXX:;

run;

icicleana
Calcite | Level 5

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!

art297
Opal | Level 21

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.

art297
Opal | Level 21

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

leonh
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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

art297
Opal | Level 21

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.

leonh
Calcite | Level 5

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

Linlin
Lapis Lazuli | Level 10

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

Tom
Super User Tom
Super User

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.

Linlin
Lapis Lazuli | Level 10

Thank you Tom!

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 15 replies
  • 7736 views
  • 7 likes
  • 5 in conversation