topic Re: Create table with incremental rows and years for columns in SAS Programming

Create table with incremental rows and years for columns

rrpking — Thu, 07 Jun 2018 15:24:38 GMT

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.

Re: Create table with incremental rows and years for columns

ballardw — Thu, 07 Jun 2018 15:44:48 GMT

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;

run;

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

Re: Create table with incremental rows and years for columns

Astounding — Thu, 07 Jun 2018 16:15:03 GMT

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;

Re: Create table with incremental rows and years for columns

rrpking — Fri, 08 Jun 2018 12:49:35 GMT

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

Re: Create table with incremental rows and years for columns

Astounding — Fri, 08 Jun 2018 15:10:19 GMT

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;

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.

Re: Create table with incremental rows and years for columns

ybolduc — Fri, 08 Jun 2018 18:00:59 GMT

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;

run;

%MEND;