topic Re: Array and do loop? in SAS Procedures

Array and do loop?

jcis7 — Fri, 07 Apr 2017 01:57:57 GMT

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

Re: Array and do loop?

Reeza — Fri, 07 Apr 2017 03:26:54 GMT

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

Re: Array and do loop?

Jagadishkatam — Fri, 07 Apr 2017 03:28:05 GMT

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;

Re: Array and do loop?

RW9 — Fri, 07 Apr 2017 08:50:18 GMT

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

Re: Array and do loop?

jcis7 — Fri, 07 Apr 2017 23:57:19 GMT

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

Re: Array and do loop?

jcis7 — Tue, 11 Apr 2017 00:08:06 GMT

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

Re: Array and do loop?

Reeza — Tue, 11 Apr 2017 00:43:55 GMT

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.

Re: Array and do loop?

jcis7 — Tue, 11 Apr 2017 17:16:19 GMT

OK. Thanks!