Array and do loop?

[jcis7]

Hi,

I need to replace all variables labeled percent variables (which are actually number values from 0 to 100) pct_var1 pct_var2 etc... that are 110 with a  --*

I then need to replace all the associated number values (labeled num_var1 num_var2) where the pct was 110 with a --*

I then need to export to excel spreadsheet.  How do I do that?  Thanks! 

data want;

set have;

array pcts pct_var1 pct_var2

nums num_var1 num_var2

if pcts=100 then do;

pcts=' -*';

nums= ' -*';

run;

I am getting an error meessage: 

ERROR: Array subscript out of range at line 866 column 8

[Reeza]

Do you have SAS 9.4 M3?

If so, you can create a format, apply it to the variables and use PROC PRINT 

http://www2.sas.com/proceedings/sugi30/001-30.pdf

If not, you'll have to convert it to a character variable to export it to Excel. 

You have no do loop or array declarations in your code? 

http://www2.sas.com/proceedings/sugi30/242-30.pdf

[Jagadishkatam]

Please try

data want;
set have;
array pcts pct_var1-pct_var110;
array nums num_var1-num_var110;
do over pcts;
if pcts=100 then do;
pcts=' -*';
nums= ' -*';
end;
end;
run;

[RW9 · Accepted solution]

Posting test data in the form of a datastep will get you better answers.  As such this is not tested.  Do note that as your variables are all number you cannot put "--" in, only numbers can go in.  You would need to change them to character to do it that way, hence why we use format here:

proc format;
  value pct
    110="--";
  value nm
    -1="--";
run;

data want;
  set have;
  array pcts{*} pct_:;
  array nums{*} num_:;
  do i=1 to dim(pcts);
    if pcts{i}=110 then nums{i}=-1;
  end;
  format pct_: pct.;
  format num_: nm.;
run;

So the format displays "--" for pcts if 110, and if its 110 then num gets changed to -1, which when formatted displays "--".  

[jcis7]

Thanks. Where do I put the actual names of the variables in the array statement?

[jcis7]

Why do the formats show in the SAS table but when I export to Excel, it reverts to the negative number?  Thanks!

[Reeza]

PROC EXPORT doesn't export formats. 

The log says so 😉

Use ODS if you want formats to be passed through, thout that has its own issues as well.

[jcis7]

OK. Thanks!