Array in Macro

[Xiaoyi]

I want to use ‘array’ in MACRO, BUT it did not work as I didn’t get the dataset I want. I used “DIM” fist, it didn’t work, so I found someone suggest using the following but it did not work either. Could some tell what’s wrong and how I can make the array work? Thank you very much. The following 'Array' is just part of my MACRO CODE. This part of code did not seem to work.

% MACRO SYS (SYS, OUTFILE);

....

data zip;

set zip;

array num[*] _numeric_;

  %do i=1 %to %sysfunc(countw(num));

   %if num[i]=. %then num[i]=0;

   %end;

drop _LABEL_ _NAME_ i;

run;

....

%MEND; 

[PaigeMiller]

Why do you need a macro at all? Array and DATA step commands are all you need. I don't see how the macro produces any benefits, even if you could get it to work.

data zip1;
    set zip;
    array num[*] _numeric_;
    do i=1 to dim(num);
       if num[i]=. then num[i]=0;
    end;
    drop i;
run;

[PaigeMiller]

Adding: I don't really see a reason to replace missings with zeros. This usually should not be done, unless you have some really good reason to do so, because it throws off all of your statistics and additional analyses.

[Xiaoyi]

Thanks for your response. I want to export the datafile to excel with all cells with missing value filled with "0" to indicate that there is 0 cases in this category, that is why I write this array to resolve this. 

[Tom]

How are you exporting to EXCEL?  Why not just use the MISSING option to have missings printed as 0?

ods excel file='myfile.xlsx' ;
options missing='0';
proc print data=mydata;
run;
ods excel close;
options missing='.';

[Xiaoyi]

Thank you for all your input, I know you guys know better ways. As I said, this code is just part of a bigger MACRO. I don't know if this makes sense to you, but I am posting the overall code. The code needs to be repeated for a dozen of times to produce a dozen datafile at the end as part of my report. See if you can spot the problem or make it better. I run it, every thing else works but the "Array" function. 

%MACRO SYS (SYS, OUTFILE);
proc freq data=zip noprint;
table year*birthzip/norow nocol nopercent out=zipct;
where syst=&SYS;
run;

proc sort data=zipct;
by birthzip year; run;

proc transpose data=zipct out=zip_w;
var count;
by birthzip;
id year;
run;

data zip_w;
set zip_w;
array num[*] _numeric_;
%do i=1 %to %sysfunc(countw(num));
%if num[i]=. %then num[i]=0;
%end;
drop _LABEL_ _NAME_ i;
_96_00=sum(_1996, _1997, _1998, _1999, _2000);
_01_05=sum(_2001, _2002, _2003, _2004, _2005);
_06_10=sum(_2006, _2007, _2008, _2009, _2010);
_11_16=sum(_2011, _2012, _2013, _2014, _2015, _2016);
Total=SUM(_1996, _1997,_1998, _1999, _2000, _2001, _2002, _2003, _2004, _2005, _2006, _2007, _2008, _2009, _2010, _2011, _2012, _2013, _2014, _2015, _2016);
run;

data zip_w;
retain birthzip Total _1996 _1997 _1998 _1999 _2000 _2001 _2002 _2003 _2004 _2005 _2006 _2007 _2008 _2009 _2010 _2011 _2012 _2013 _2014 _2015 _2016
;
set zip_w;
run;

PROC SORT DATA=ZIP_W OUT=&OUTFILE;
BY DESCENDING Total ;
RUN;

PROC EXPORT DATA=&OUTFILE
OUTFILE="\\ACCIT2\ZIP\&OUTFILE"
dbms=csv replace;
RUN;
%MEND;

[PaigeMiller]

data zip_w;
set zip_w;
array num[*] _numeric_;
%do i=1 %to %sysfunc(countw(num));
%if num[i]=. %then num[i]=0;
%end;

Regarding the above portion of your code:

There is no need to use macro commands or functions or %DO or %IF here, this will not work and provides no benefit even if you could get it to work. The code I provided earlier in this thread ought to work here. But the solution from @Tom makes the whole thing simple, and ARRAY is not necessary either (although it will work).

[Tom · Accepted solution]

Just because you are using a macro to generate/run some SAS code does not mean you need to modify the SAS code you are running.  So just use a DO loop, not a %DO loop in the data step.

You can also simplify the code using variable lists.

Also a CSV file is NOT and EXCEL file. It is just a text file.

%MACRO SYS (SYS, OUTFILE);
proc freq data=zip noprint;
  table birthzip*year/norow nocol nopercent out=zipct;
  where syst=&SYS;
run;

proc transpose data=zipct out=zip_w prefix=_;
  var count;
  by birthzip;
  id year;
run;

data zip_w;
  retain birthzip Total _1996-_2016 ;
  set zip_w;
  array num[*] _numeric_;
  do i=1 to dim(num);
    num[i]=coalesce(num[i],0);
  end;
  _96_00=sum(of _1996-_2000);
  _01_05=sum(of _2001-_2005);
  _06_10=sum(of _2006-_2010);
  _11_16=sum(of _2011-_2016);
  Total=sum( of _1996-_2016);
  drop _LABEL_ _NAME_ i;
run;


PROC SORT DATA=ZIP_W OUT=&OUTFILE;
  BY DESCENDING Total ;
RUN;

PROC EXPORT DATA=&OUTFILE OUTFILE="\\ACCIT2\ZIP\&OUTFILE"
  dbms=csv replace;
RUN;
%MEND SYS;

[Xiaoyi]

Tom's modified codes run perfectly. I got what exactly I want. Thanks a lot.