Create table with incremental rows and years for columns

[rrpking]

Hello,

I'm looking to create a new table with 0's as data points

I want it to look like this:

Year/Quarter                  3           6           9           12

2014

2015

2016

2017

2018

I have the year set up like this: 

data want_yr;
date=&FirstYear;
do while (date<=&CurrentYr);
output;
date = date+1;
end;
run;

But I'm pretty confused on how to continue.

[ballardw]

Please describe what you will do with the resulting data?

There is at least one problem, you can't have  variables named 3, 6, 9 or 12.

Which variables get 1 and which 0?

Second I can see a lot of reasons to use quarter values of 1, 2, 3, and 4.

What values are your macro variables &firstyear and &currentyear ?

For what you show so far a do loop with

do year = &firstyear to &currentyear;

   /* assignment of the "quarter variables" would go here*/

   output;

end;

is simpler.

For a very large number of data reasons I would likely do

data want;

do year = &firstyear to &currentyear;

   do quarter = 1 to 4;

      output;

   end;

end;

run;

Put for any other values, such as your "0 1" we need some rule.

[Astounding · Accepted solution]

As was already noted, you can't use variable names like 3, 6, 9, etc.  So you may want to replace the choices that I'm making below.  here's what I think you are after:

data want;

do year = &FirstYear to &CurrentYr;

   output;

end;

retain m_03 m_06 m_09 m_12 0;

run;

[rrpking]

That does look better! Thank you! Now I have to figure out how to make it from m_03 to m_360.

[Astounding]

To save on typing, while going to 360, you might consider a two-step approach:

data step1;

value=0;

do year = &FirstYear to &CurrentYr;

   do m = 3 to 360 by 3;

      varname = 'm_' || put(m, z3.);

      output;

   end;

end;

run;

You can inspect that if you would like, before continuing:

proc transpose data=step1 out=want (drop=_name_);

   by year;

   var value;

   id varname;

run;

Also consider whether you might be better off omitting step 2 entirely, and just using the data set STEP1.

[ybolduc]

You could simply wrap it in a macro and use a macro loop. Something like this:

%MACRO myMac;

  data want;

    do year = &FirstYear to &CurrentYr;

      output;

    end;

    retain

      %DO I = 1 %TO 120;

        m_%EVAL(&I.*3)

      %END;

    0;

  run;

%MEND;