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!
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
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
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!
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
if you don't have other tables in the same library starting with XXXXX then you could do:
data one;
set XXXXX:;
run;
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!
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.
First, I have a question for you. How many variables are in work.test and what are the variable names for each?
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
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
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.
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
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
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.
Thank you Tom!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.