DATA Step, Macro, Functions and more

A simple macro?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

A simple macro?

Dear All,

I would describe my knowledge and abilities in programing and macros as basic. This is my problem:

I have a dataset. I will refer to it as "history". In history, each observation is a summary of a report, including a date of issue and various variables. Reports are issued at different times, i.e. the difference in months between a report and a previous report could be any value between 1 and 24.

I would like to create a new dataset. In this dataset the first observation would be the most recent report in history. The second observation would be the next and closest report in history, conditional on the fact it was issued at least 6 months later than the report in the FIRST observation. The third observation would now be the next report in history, conditional on the fact it was issued at least 6 months later than the report in the SECOND observation, and so forth. In other words, I have a reference date that needs to be updated.

1. Can this be done without a macro? How?

2. Must this be done with a macro? How?

Any help would be highly appreciated.

Michael


Accepted Solutions
Solution
‎05-26-2015 01:34 AM
Contributor dkb
Contributor
Posts: 53

Re: A simple macro?

Posted in reply to MichaelvanStraten

Dr van Straten, didn't you broadcast on LBC some years ago?  What a surprise!

Try this:

data aborthist;

set aborthist;

by descending startdate;

retain last_kept; /* remember its value from row to row  */

drop last_kept;  /* but do not bother to write it to the output  */

if _n_=1 then do; /* definitely want the first one */

     output;

     last_kept = startdate; /* keep a note of the date we just wrote */

end;

else if last_kept - startdate ge 180 /* compare current date to last one written */

     then do;

     output;

     last_kept = startdate; /* and this is the one we compare next time */

end;

run;


View solution in original post


All Replies
Super User
Posts: 5,426

Re: A simple macro?

Posted in reply to MichaelvanStraten

1. Yes. Sort your data on report id and descending report date. Use a data step with BY report date. Use retain (with firs. logic) or lag() to compare report date between observations. Keep records that meets your criteria.

2. No.

Data never sleeps
Occasional Contributor
Posts: 14

Re: A simple macro?

Thank you. I'll try that.

Occasional Contributor
Posts: 14

Re: A simple macro?

Posted in reply to MichaelvanStraten

Sorry, this solution is not working;

I used:

data aborthist;

set aborthist;

by descending startdate;

if _n_=1 OR (lag(startdate)-startdate ge 180) then output;

run;

Apparently, the lag function queues the value of the previous observation. However, the previous observation is not necessarily the observation I need for the reference date. The reference date is the date belonging to the previous observation THAT WAS KEPT. For example, I have the  dates:

May 2014

Dec 2013

Jun 2013

Mar 2013

Jan 2013

Aug 2012

Mar 2012

Jan 2011

Sep 2011

Mar 2011

The following reports SHOULD be extracted: May 2014, Jun 2013, Aug 2012, Dec 2012, Mar 2011.

However, only the following reports ARE extracted: May 2014, Jun 2013, March 2011.

Solution
‎05-26-2015 01:34 AM
Contributor dkb
Contributor
Posts: 53

Re: A simple macro?

Posted in reply to MichaelvanStraten

Dr van Straten, didn't you broadcast on LBC some years ago?  What a surprise!

Try this:

data aborthist;

set aborthist;

by descending startdate;

retain last_kept; /* remember its value from row to row  */

drop last_kept;  /* but do not bother to write it to the output  */

if _n_=1 then do; /* definitely want the first one */

     output;

     last_kept = startdate; /* keep a note of the date we just wrote */

end;

else if last_kept - startdate ge 180 /* compare current date to last one written */

     then do;

     output;

     last_kept = startdate; /* and this is the one we compare next time */

end;

run;


Occasional Contributor
Posts: 14

Re: A simple macro?

Sorry to disappoint you dkb, I'm not THE Michael van Straten (Health Journalist and much more), I'm just a Michael van Straten (Vet).

Thank you very much for your solution. I'll check it out but am already quite confident it will work as you have addressed the key problem. So I know for the future: if one refers to a variable AFTER using the output command (like you did above), will the value of the variable always be that belonging to the observation that was "output"?

Super User
Posts: 7,766

Re: A simple macro?

Posted in reply to MichaelvanStraten

The OUTPUT statement does not change any variable values at all; it just writes the contents of the PDV to the dataset.

Variable values change:

- when an input dataset is read (SET or MERGE statement)

- when values are assigned through direct assignment (=), incrementation (like x + 1Smiley Wink or by a "call" subroutine

- when a new data step iteration begins (all variables not RETAINED are set to missing)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 14

Re: A simple macro?

Worked perfect! Thanks.

Super User
Posts: 7,766

Re: A simple macro?

Posted in reply to MichaelvanStraten

Since the first observation is the most recent report, by definition there can be no later report. So I guess you meant "earlier"?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 14

Re: A simple macro?

Posted in reply to KurtBremser

Yes, sorry. Earlier of course.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 305 views
  • 0 likes
  • 4 in conversation