Hi, I have the input data, output data I'd like a well as the code below.
A few questions:
1) How can I get SAS to output what I showed below into an Excel spreadsheet with the symbol: ≥
2) When I use the 'do over' language and I have two array names in the 'do over' loop, does it make a difference which name I use? For example, I use: do over pcts. But I also have the array name 'nums' in the if-then statement.
3) Is it OK to put all the if-then statemtents in one 'do over' statement or break them up into multiple do over statements?
data have; |
input buildingname & $20. population vaxa_num pctvaxa vaxb_num pctvaxb vaxd_num pctvaxd buildingtype $; |
cards; |
happy days 100 99 99 96 96 0 0 private |
learning tree 20 18 90 19 95 2 10 |
bean stalk 50 50 100 0 0 50 100 |
data want; |
set have; |
array pcts pct_vaxa pct_vaxb pct_vaxd; |
array nums vaxa_num vaxb_num vaxd_num; |
array pct $5 vaxa_pct vaxb_pct vaxd_pct; array num $5 vaxa vaxb vaxd;
do over pcts; pct=put (pcts, 4.);
end; do over nums; num=put(nums, 4.); end; |
do over pcts; |
if1=< population<=39 and pcts >=95 then pct='>=95': |
if1=< population<=39 and pcts >=95 then num='--': |
if 40=<population<=99 and pcts >=98 then pct='>=98': |
if 40=<population<=99 and pcts >=98 then num='--': |
if population>=100 and pcts >=99 then pct='>=99': |
if population>=100 and pcts >=99 then num='--': |
end; |
run; |
Final Output | |||||||
buildingname | population | vaxa_num | pct_vaxa | vaxb_num | pct_vaxb | vaxd_num | pct_vaxd |
happy days | 100 | -- | ≥99% | -- | ≥99% | 0 | 0 |
learning tree | 20 | 18 | 90 | -- | ≥95% | 2 | 10 |
bean stalk | 50 | -- | ≥98% | 0 | 0 | -- | ≥98% |
Thanks!
How to get a "greater than or equal to" symbol is explained, and an example is given at:
https://www.pharmasug.org/proceedings/2013/CC/PharmaSUG-2013-CC30.pdf
2. Do OVER loops aren't supported and not even documented since SAS 7. I would recommend against using them, though so far SAS has ensured backwards compatibility. Using outdated unsupported methods is not a good habit to get into, in SAS or any other language. FYI - this is an opinion not a requirement.
3. It's fine to put everything into one DO loop. If only one condition in the list can be met then use IF/ELSE IF, not multiple IF statements.
Thank you for posting a well formed question with sample data etc.
Unfortunately I have no idea on how to do #1 at the moment. I would search the Vince DelGobbo's SAS tagset index paper to see if it had any examples on how to accomplish inserting special characters. You don't specify how you're exporting but I would recommend ODS EXCEL.
https://vasug.files.wordpress.com/2011/07/excelxppaperindex.pdf
Proc export, is quite basic, doesn't have any formatting possibilities. Does create native Excel file.
Ods tagsets.excelxp, has a lot more flexibility in formatting, layout and such like. Does not create native Excel file, creates an XML file which Excel can read.
There is also Libname excel, and the lastest one Libname xlsx. And of course you could always output to csv.
ODS EXCEL creates a new Excel workbook each time. You cannot append to another workbook o use a template.
Right, after some time I have got some working code. In future could you make sure that what you post actually runs, ther are special characters in there and loads of typos and other errors. Also, your arrays dimensions do not match up, num only has 3 elements, the other have 5.
data have; infile datalines missover; input buildingname $20. population vaxa_num pctvaxa vaxb_num pctvaxb vaxd_num pctvaxd buildingtype $; cards; happy days 100 99 99 96 96 0 0 0 0 0 private learning tree 20 18 90 19 95 2 10 bean stalk 50 50 100 0 0 50 100 ; run; data want; set have; array pcts pct_vaxa pct_vaxb pct_vaxd; array nums vaxa_num vaxb_num vaxd_num; array pct $5 vaxa_pct vaxb_pct vaxd_pct; array num $5 vaxa vaxb vaxd; do i=1 to dim(pcts); pct{i}=put(pcts{i},4.); num{i}=put(nums{i},4.); if 1 <= population <= 39 and pcts{i} >= 95 then do; pct{i}='>=95'; num{i}='--'; end; if 40 <= population <= 99 and pcts{i} >= 98 then do; pct{i}='>=98'; num{i}='--'; end; if population >= 100 and pcts{i} >= 99 then do; pct{i}='>=99'; num{i}='--'; end; end; run;
As for outputting less than equals, depends on how you are outputting the data.
Please post code in a code box opened using the forum {i} menu icon. The main message window sometimes ends up with characters you didn't intend going through the paste option for whatever you copied to your clipboard. And please start with code from the SAS editor or other plain text editor. The lines appearing in your example code indicate the source was possibly a program that inserts lots of extra junk. Also the message window will strip characters and possibly compress things.
Just copying your data have step and running results in a log that looks like:
NOTE: Invalid data for population in line 7 1-8. NOTE: Invalid data for vaxa_num in line 7 10-13. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-- 7 learning tree 20 18 90 19 95 2 10 buildingname=happy days 100 99 99 population=. vaxa_num=. pctvaxa=20 vaxb_num=18 pctvaxb=90 vaxd_num=19 pctvaxd=95 buildingtype=2 _ERROR_=1 _N_=1 NOTE: LOST CARD. 9 ; buildingname=bean stalk 50 50 100 population=. vaxa_num=. pctvaxa=. vaxb_num=. pctvaxb=. vaxd_num=. pctvaxd=. buildingtype= _ERROR_=1 _N_=2 NOTE: SAS went to a new line when INPUT statement reached past the end of a line. NOTE: The data set USER.HAVE has 1 observations and 9 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds
So there were errors in that data step that make it hard to work with. Hence @RW9's comment.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.