BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcis7
Pyrite | Level 9

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

View solution in original post

7 REPLIES 7
Reeza
Super User

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
Amethyst | Level 16
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;
Thanks,
Jag
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Pyrite | Level 9
Thanks. Where do I put the actual names of the variables in the array statement?
jcis7
Pyrite | Level 9

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

Reeza
Super User

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
Pyrite | Level 9
OK. Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1035 views
  • 2 likes
  • 4 in conversation