Solved
New Contributor
Posts: 3

# Create table with incremental rows and years for columns

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.

Accepted Solutions
Solution
2 weeks ago
Super User
Posts: 6,637

## Re: Create table with incremental rows and years for columns

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;

All Replies
Super User
Posts: 13,338

## Re: Create table with incremental rows and years for columns

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.

Solution
2 weeks ago
Super User
Posts: 6,637

## Re: Create table with incremental rows and years for columns

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;

New Contributor
Posts: 3

## Re: Create table with incremental rows and years for columns

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

Super User
Posts: 6,637

## Re: Create table with incremental rows and years for columns

[ Edited ]

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.

Occasional Contributor
Posts: 12

## Re: Create table with incremental rows and years for columns

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;

☑ This topic is solved.