## Array and do loop?

Solved
Regular Contributor
Posts: 206

# Array and do loop?

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

Accepted Solutions
Solution
‎04-10-2017 12:04 PM
Super User
Posts: 9,599

## Re: Array and do loop?

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 "--".

All Replies
Super User
Posts: 23,677

## Re: Array and do loop?

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

Posts: 1,147

## Re: Array and do loop?

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;
Thanks,
Jag
Solution
‎04-10-2017 12:04 PM
Super User
Posts: 9,599

## Re: Array and do loop?

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 "--".

Regular Contributor
Posts: 206

## Re: Array and do loop?

Thanks. Where do I put the actual names of the variables in the array statement?
Regular Contributor
Posts: 206

## Re: Array and do loop?

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

Super User
Posts: 23,677

## Re: Array and do loop?

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.

Regular Contributor
Posts: 206

## Re: Array and do loop?

OK. Thanks!
☑ This topic is solved.