BookmarkSubscribeRSS Feed
Calcite | Level 5

Usually work with 1 record per entity. Not a frequent SAS user.........I've tried everything I know.


I have multiple records for each entity. Each record has a field(s) that has been assigned a Y or N based on certain criteria.


Given that SAS processes 1 record at a time, how can I test "across records" to get a single result? For instance, if a field in record number 1 = N and a different field from record 2 = N...........



school code, referendum date, referendum type and result

0007, 8/5/2016, operating_referendum_passed_flag = N

0007, 10/19/2017, debt_referendum_passed_flag = N


Version 9.1




Opal | Level 21

Separate type and result. Then you can manipulate. For example:


data have;
infile datalines dsd;
input code $ date :anydtdte10. type_result :$64.;
format date yymmdd10.;
length type $40 result $1;
type = scan(type_result,1,"=");
result = strip(scan(type_result,2,"="));
drop type_result;
0007, 8/5/2016, operating_referendum_passed_flag = N
0007, 10/19/2017, debt_referendum_passed_flag = N

proc sql;
create table o_d_cases as
    o.code, as o_date,
    o.result as o_result, as d_date,
    d.result as d_result
    have as o inner join 
    have as d on o.code = d.code and <
where o.type = "operating_referendum_passed_flag" and
    d.type = "debt_referendum_passed_flag";
title "Operating - debt cases"; 
select * from o_d_cases;
Calcite | Level 5

I don't think I asked my question correctly. This is what I need to do......some school districts run multiple referenda to get authority to raise taxes. Some district fail, then subsequently pass a referendum. Base on testing if this did occur, they then get additional other taxing authority.


So I need to determine, for each entity that has both passed and failed a referendum in the past 3 years (so, the entity will have multiple records, one each for each attempted referendum), if the date of the "passed" referendum is more recent than that of the failed.


My thought was to test each record and get a numeric result so I can then combine all the records into 1 record for the entity (which I will do with proc summary) to make comparisons within the entity record to see if there was a fail/pass situation, as opposed to trying to compare across multiple records. I have never worked with more than one record per entity, so this is causing me a bit of grief.


My thought was to assign a "1" to each passed/failed response so then I can use the proc summary. For the entity 14 below, I should get a 3 in the failed field (after the proc summary) because they had 3 failed referenda. (My second problem is converting dates from an oracle database so SAS can compare and rank in chronological order.....)


I don't have the loop code I used with me at home right now but it's not working anyhow.....I am getting (for entity 14, let's say)  not only the counts for the entity 14 (which should be 3 failed), I was also getting counts for "passed" from the previous record in the summary record. (proc summary is by entity)


I think they call this control break processing and I need to clear the fields with each new entity, but it's not working....I think the code is not too complex, but I don't have much experience. Thoughts on how to do this?


entityvote datepassedfailed
71/18/2016 Y
710/18/2016 Y
142/16/2017 Y
148/26/2017 Y
149/15/2017 Y



SAS Employee

First, I would suggest you to read in the dates using an informat like below, since you will need to compare dates in the process.



Here is your data recreated:

data test;
   infile datalines missover dsd dlm=' ';
   input entity vote_date :mmddyy10. passed $ failed $;
7 10/25/2015 Y 
7 1/18/2016  Y
7 10/18/2016  Y
14 2/16/2017  Y
14 8/26/2017  Y
14 9/15/2017  Y

Then use a RETAIN statement to keep track of the most recent date for passed and failed referendum (I used lastpassed_date and lastfailed_date to hold their values).



data test2;
   set test;
   by entity vote_date;
   retain lastpassed_date lastfailed_date;

   if first.entity then do;
   if passed='Y' then lastpassed_date=vote_date;
   else if failed='Y' then lastfailed_date=vote_date;

Now that you have these dates stored, you can compare them at the end of the program. Sounds like you might only want to keep the records where lastpassed_date is greater than lastfailed_date, making sure to check that lastfailed_date isn't missing.



if lastpassed_date > lastfailed_date and not missing(lastfailed_date);

Let me know if this works. 



Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 3 in conversation