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
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 "--".
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?
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 "--".
Why do the formats show in the SAS table but when I export to Excel, it reverts to the negative number? Thanks!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.