How to improve my SAS code

[samira]

I have below scripts in my program, I am looking for a better way to do same functionality but to avoid repetitive statements.

Here are the examples: 

data the_matrix_query;  
   set the_matrix_query_withnull;
   if year2009=" " then year2009=0;
   if year2010=" " then year2010=0;
   if year2011=" " then year2011=0;
   if year2012=" " then year2012=0;
   if year2013=" " then year2013=0;
   if year2014=" " then year2014=0;
   if year2015=" " then year2015=0;
   if year2016=" " then year2016=0;
   if year2017=" " then year2017=0;
   run;

  data recomputationLimits;
  set highchart_data(keep=ucl);
  set final_query;
 
   
  if ucl<year2009 then year2009=null; 
  if ucl<year2010 then year2010=null; 
  if ucl<year2011 then year2011=null; 
  if ucl<year2012 then year2012=null; 
  if ucl<year2013 then year2013=null; 
  if ucl<year2014 then year2014=null; 
  if ucl<year2015 then year2015=null; 
  if ucl<year2016 then year2016=null; 
  if ucl<year2017 then year2017=null; 
  
  run;

data y2009;set the_matrix_query(keep=week year2009 where=(week<53)); year=2009; count=year2009;run;
data y2010;set the_matrix_query(keep=week year2010 where=(week<53)); year=2010; count=year2010;run;
data y2011;set the_matrix_query(keep=week year2011 where=(week<53)); year=2011; count=year2011;run;
data y2012;set the_matrix_query(keep=week year2012 where=(week<53)); year=2012; count=year2012;run;
data y2013;set the_matrix_query(keep=week year2013 where=(week<53)); year=2013; count=year2013;run;
data y2014;set the_matrix_query(keep=week year2014); year=2014; count=year2014;run;
data y2015;set the_matrix_query(keep=week year2015 where=(week<53)); year=2015; count=year2015;run;
data y2016;set the_matrix_query(keep=week year2016 where=(week<53)); year=2016; count=year2016;run;
data y2017;set the_matrix_query(keep=week year2017 where=(week<53)); year=2017; count=year2017;run;

proc sql;
create table makevector as
select week, year, count from y2009 
union all select week, year, count from y2010 
union all select week, year, count from y2011
union all select week, year, count from y2012 
union all select week, year, count from y2013
union all select week, year, count from y2014
union all select week, year, count from y2015 
union all select week, year, count from y2016
union all select week, year, count from y2017;
quit;

I would appreciate you help.

[novinosrin]

Please use arrays or user defined formats look up using proc format and search online for examples. You can find tons of them on a simple google search.

[Reeza]

In addition to @novinosrin solution, you can also use PROC TRANSPOSE for the last two steps, which I think is what you're trying to do there. Basically reformatting a data from a wide to a long data set.

[Patrick]

@samira

Additionally to what has been said already:

- If you have a list of exclusive IF statements then start using ELSE IF

- You could express your SQL UNION statement also via a SAS data step as below:

data makevector;
  set Y2009 - y2017 (keep=week year count);
run;

As long as the variables in your sources have the same attributes - mainly the same lengths - the result of the SQL UNION and the data step approach will be the same. 

If lengths differ (which I'd call a DQ problem) then the SQL UNION will created a variable in the result set with the longest length from source but the data step will use the length from the first data set in the SET statement - which can lead to string truncation for sources with a longer length.

[ChrisNZ]

if year2009=" " then year2009=0;

is wrong.

Is YEAR2009 a numeric or a character variable?

Code for the correct type.

[Kurt_Bremser]

Start with modeling your data right. Instead of

id year2009 year2010 year2011

have

id year value

and your code will shrink to a few lines and be easier to maintain by orders of magnitude.

Intelligent data creates intelligent programs (see Maxim 19 and Maxim 33).

[s_lassen]

Using arrays is generally the way to do these things. For the first step, I would use the COALESCE function:

data the_matrix_query;

  set the_matrix_query_withnull;

  array years(*) 8 year:;

  do _N_=1 to dim(years);

    years(_N_)=coalesce(years(_N_),0);

    end;

run;

Of course, you may want to make the array data explicit (year2009-year2017) instead of year:, if you have other variable names beginning with "year".

The second step can be done in a similar way, except that you cannot use coalesce, but must use if...then.

The last two steps can be put into a single step:

data makevector;

  set the_matrix_query;

  where week<53;

  array years(2009:2017) 8 year2009-year2017;

  do year=2009 to 2017;

    count=years(year);

    output;

    end;

  keep week year count;

end;

I am not sure why you did not put the week<53 condition on 2014. Is that because there was no week 53 in 2014, or because you really want week 53 included for 2014 specifically? In the latter case, you will have to change the code a bit, e.g.:

data makevector;

  set the_matrix_query;

  array years(2009:2017) 8 year2009-year2017;

  if week<53 then do year=2009 to 2017;

    count=years(year);

    output;

    end;

  else do;

    count=year2014;

    year=2014;

    output;

    end;

  keep week year count;

end;