BookmarkSubscribeRSS Feed
jcis7
Pyrite | Level 9

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!

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Reeza
Super User

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

jcis7
Pyrite | Level 9
Thank you for your opinion - I appreciate it and am taking it to heart. Thank you for answering the question about putting everything in one do loop.
I found the following on ODS. What benefits are there to using ODS vs Proc Export? Thanks.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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. 

jcis7
Pyrite | Level 9
Thanks. If I want to use ODS and export to Excel as an xlsx spreadsheet, is it possible to have SAS replace the spreadsheet each time? Or is that possible only in Proc Export?
Reeza
Super User

ODS EXCEL creates a new Excel workbook each time. You cannot append to another workbook o use a template. 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

jcis7
Pyrite | Level 9
Appreciate you taking the time to work through the data I posted and come up with a solution I am now using. Appreciate your help and time. Thank you.
ballardw
Super User

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.

 

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!

How to Concatenate Values

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.

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
  • 9 replies
  • 1612 views
  • 2 likes
  • 5 in conversation