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.
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;
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 ¤tyear ?
For what you show so far a do loop with
do year = &firstyear to ¤tyear;
/* 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 ¤tyear;
do quarter = 1 to 4;
output;
end;
end;
run;
Put for any other values, such as your "0 1" we need some rule.
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;
That does look better! Thank you! Now I have to figure out how to make it from m_03 to m_360.
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.