DATA Step, Macro, Functions and more

Greater than or equal to sign, array, do loop

Reply
Regular Contributor
Posts: 192

Greater than or equal to sign, array, do loop

[ Edited ]

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!

Trusted Advisor
Posts: 1,621

Re: Greater than or equal to sign, array, do loop

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

Super User
Posts: 17,868

Re: Greater than or equal to sign, array, do loop

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

Regular Contributor
Posts: 192

Re: Greater than or equal to sign, array, do loop

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.
Super User
Super User
Posts: 7,411

Re: Greater than or equal to sign, array, do loop

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. 

Regular Contributor
Posts: 192

Re: Greater than or equal to sign, array, do loop

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?
Super User
Posts: 17,868

Re: Greater than or equal to sign, array, do loop

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

 

Super User
Super User
Posts: 7,411

Re: Greater than or equal to sign, array, do loop

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.

Regular Contributor
Posts: 192

Re: Greater than or equal to sign, array, do loop

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.
Super User
Posts: 10,516

Re: Greater than or equal to sign, array, do loop

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.

 

Ask a Question
Discussion stats
  • 9 replies
  • 177 views
  • 2 likes
  • 5 in conversation