topic Re: Create several tables by year from one original table in SAS Programming

Create several tables by year from one original table

Dynamike — Thu, 04 Oct 2018 14:09:49 GMT

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

Re: Create several tables by year from one original table

RW9 — Thu, 04 Oct 2018 14:15:44 GMT

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;
...

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;

Re: Create several tables by year from one original table

kiranv_ — Thu, 04 Oct 2018 14:22:49 GMT

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

Re: Create several tables by year from one original table

novinosrin — Thu, 04 Oct 2018 14:25:39 GMT

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

Re: Create several tables by year from one original table

SuryaKiran — Thu, 04 Oct 2018 14:38:03 GMT

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;