topic Re: Determine result across multiple records in SAS Programming

Determine result across multiple records

Karenk1 — Mon, 16 Apr 2018 18:18:31 GMT

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...........

Data:

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

Re: Determine result across multiple records

PGStats — Mon, 16 Apr 2018 18:55:36 GMT

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;
datalines;
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
select 
    o.code,
    o.date as o_date,
    o.result as o_result,
    d.date as d_date,
    d.result as d_result
from
    have as o inner join 
    have as d on o.code = d.code and o.date < d.date
where o.type = "operating_referendum_passed_flag" and
    d.type = "debt_referendum_passed_flag";
title "Operating - debt cases"; 
select * from o_d_cases;
quit;

Re: Determine result across multiple records

Karenk1 — Sun, 22 Apr 2018 01:15:49 GMT

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?

entity	vote date	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

Re: Determine result across multiple records

antonbcristina — Mon, 23 Apr 2018 17:37:41 GMT

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.

:mmddyy10.

Here is your data recreated:

data test;
   infile datalines missover dsd dlm=' ';
   input entity vote_date :mmddyy10. passed $ failed $;
datalines;
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
run;

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;
      lastpassed_date=.;
      lastfailed_date=.;
   end;
   if passed='Y' then lastpassed_date=vote_date;
   else if failed='Y' then lastfailed_date=vote_date;
run;

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.