topic Re: Output three years with suffix in SAS Programming

Output three years with suffix

Dynamike — Mon, 03 Dec 2018 14:29:02 GMT

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

Re: Output three years with suffix

PaigeMiller — Mon, 03 Dec 2018 14:37:47 GMT

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.

Re: Output three years with suffix

Kurt_Bremser — Mon, 03 Dec 2018 14:39:59 GMT

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;

Re: Output three years with suffix

novinosrin — Mon, 03 Dec 2018 14:38:40 GMT

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 ;

Re: Output three years with suffix

Jagadishkatam — Mon, 03 Dec 2018 15:17:19 GMT

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;

Re: Output three years with suffix

novinosrin — Mon, 03 Dec 2018 16:06:39 GMT

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

Re: Output three years with suffix

Jagadishkatam — Mon, 03 Dec 2018 16:16:12 GMT

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

Re: Output three years with suffix

novinosrin — Mon, 03 Dec 2018 17:15:15 GMT


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)