Create several tables by year from one original table

[Dynamike]

Hi all,

i think its an easy task for you, but obviously not for me.

i have one original table looks like that:

year             information

2015             x

2016             y

2015             z

2014             x

What i want to do is to create three tables named test2014, test2015, test2016 including only the information related to the named year:

test2014:

year             information

2014            x

test2015:

year            information

2015           x

2015           z

test2016:

year            information

2016           y

i tried something like this.

year=&year.

Data Test&year;
set originaldata;
DO year=2014 to 2016;
where year=&year;
end:
run;

But i think there is some basic macro/loop knowledge missing 😕

Thank you for your effort!

Mike

[RW9]

The simple answer is don't.  There are very few, if any reasons, for splitting like data up into different datasets, and the outcome of doing so is increased storage space, more complex coding which is less robust.

If you need to create a report or do some processing on the data by year, which may be why your asking, then use the inbuilt by group processing system:

proc report...;
  by year;
...

Or

proc means....
  by year;
...

Its quicker than any other method.

As for the theoretical how to do it, which you wouldn't be doing because that is suboptimal:

proc sort data=have out=loop nodupkey;
  by year;
run;
data _null_;
  set loop;
  call execute(cats('data test',put(year,z4.),'; set have; where year=',put(year,z4.),';run;'));
run;

[kiranv_]

this is priceless advice. creating multiple datasets in your scenario can easily become maintenance nightmare, especially if you have to make changes.

[novinosrin]

These links has all that you need

https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

[SuryaKiran · Accepted solution]

Construct multiple IF conditions into a macro 

data have;
Input year information :$;
datalines;
2015 x
2016 y
2015 z
2014 x
;
run;

option symbolgen mprint mlogic;
proc sql noprint;
select distinct "if year="||put(year,4.)||" then output "||cats("test",year), cats("test",year)
			Into: if_condition separated by " ;", :ds_list separated by " "
from have;
quit;

%put "&if_condition" ;
%put &ds_list;


data &ds_list;
set have;
&if_condition;
run;