Output three years with suffix

[Dynamike]

Hi all,

i have the following data set:

ID    year       Cost

 X    2015      100

 X    2016      200

 X    2017     150

Y    2015      30

 Y    2016      70

 Y    2017     400

...

Now i want separated data tables for each year and tried:

%Macro Test;
%do i=2015 %to 2017;
Data Cost_&i;
set in;
If year=&i then output %end;
%MEND Test;

%Test

This doesnt work. Could you please help me.

Thank you for your effort!

Mike

[PaigeMiller]

First of all, I feel compelled to mention, as other people do, that separating data like this is inefficient and a poor practice. You'd be better off in most situations keeping the data in one data set and then when it comes time to do analysis, use the BY statement to get analyses for each year.

When running macros like this, you want to use OPTIONS MPRINT; before the macro so as to make the macro easier to debug.

When asking for help, saying "This doesn't work" really provides no useful information. Showing us the error (the SAS log) is a much more effective way to get help.

The most obvious mistake in your code is that you are missing a semi-colon after OUTPUT and also you need a RUN; statement after the OUTPUT; statement.

[Kurt_Bremser]

Write the data step for one year (without any macro action) and compare it to what you create with the macro.

Hint: there's something small but important missing.

hint-hint: use options mlogic mprint;

[novinosrin]

data have;
input ID $   year       Cost;
cards;
 X    2015      100
 X    2016      200
 X    2017     150
Y    2015      30
 Y    2016      70
 Y    2017     400
 ;



proc sql ;                                                              
  create index year on have (year) ;                      
quit ;                                                                  
                                                                        
data _null_ ;                                                           
  if _n_ = 1 then do ;                                                  
    dcl hash h (ordered:'y') ;                                                     
    h.definekey  ("_n_") ;                                              
    h.definedata ('id','year','cost') ;
    h.definedone () ;                                                   
  end ;                                                                 
  do _n_ = 1 by 1 until (last.year) ;                            
    set have ;                                                          
    by year ;                                                    
    h.add() ;                                                           
  end ;                                                                 
  h.output (dataset: catx ("_", "year", year)) ;          
  h.clear() ;                                                           
run ;     

[Jagadishkatam · Accepted solution]

Alternatively with call execute

data have;
input ID $   year       Cost;
cards;
 X    2015      100
 X    2016      200
 X    2017     150
Y    2015      30
 Y    2016      70
 Y    2017     400
 ;
        
proc sql;
create table years as select distinct year from have;
quit;

data _null_;
set years;
call execute('data year_'||strip(year)||'; set have; where year='||year||';run;');
run;	

[novinosrin]

@Jagadishkatam  Very nice, traditional and simple. I am glad you posted this. Thank you!

[Jagadishkatam]

I am learning from all the legends in this forum including you. I like @novinosrin coding.

[novinosrin]

data have;
input ID $   year       Cost;
cards;
 X    2015      100
 X    2016      200
 X    2017     150
Y    2015      30
 Y    2016      70
 Y    2017     400
 ;

proc sql;
select distinct year into :year  separated by ',' 
from have;
quit;
%macro split/parmbuff;
   %let num=1;
   %let dsname=%scan(&syspbuff,&num);
   %do %while(&dsname ne);
      data year_&dsname;
	  set have;
	  where year=&dsname;
	  run;
      %let num=%eval(&num+1);
      %let dsname=%scan(&syspbuff,&num);
   %end;
%mend split;

%split(&year)