May be a bit early to be diving into the macro language. The SAS macro language is intended to write code. So if you don't know what basic SAS statements you want to create the macro language isn't very helpful. If you have lots of experience in some other text parsing/reformatting it may help make logical connections of the macro elements but still need to know what the basic SAS code should look like.
Before attempting macro code (and I don't think it is actually needed if I understand your problem) you need to write the basic SAS code statements that perform what you need. Then turn it into macro code if needed.
Some questions:
Your limited example coupled with Proc Import code I would expect the SEGM_DATE variable to be a datetime value and likely have a DATETIME format, possibly DATETIME18. assigned. Is that the case? (Important note:
don't trust proc import to always guess correctly as to variable types. You increase the chance of accuracy using the Guessingrows=max; option but it can still be wrong. Better is to write data step to read CSV type files using documentation about the file contents).
Use of FIRST.<variable> and LAST.<variable> syntax requires that the variable appear on a BY statement. Which requires either the data be sorted by the By variables or the NOTSORTED option. Your attempt to parse a list of all variables and generate/use First. with them
There is a function INTCK that will return the interval between two date, time or datetime values. If the values are datetime then the interval names start with DT such as DTyear, dtmonth, dtday.
So you can test the interval between two datetime values as months with: Intck('dtmonth',dtvalue1, dtvalue2).
And companion INTNX increments values, which is useful for this sort of thing.
So with a small example data set:
data example;
input SEGM_DATE datetime18. CLIENT_ID :$9. AVG_WEIGHT;
format SEGM_DATE datetime18.;
datalines;
01MAR2012:00:00:00 1-ET-1500 8
01APR2012:00:00:00 1-ET-1500 10
01JUN2012:00:00:00 1-ET-1500 13
01JAN2012:00:00:00 2-ET-1500 12
01JUN2012:00:00:00 2-ET-1500 18
;
data want;
set example;
ldt = lag(SEGM_DATE);
/* very likely that when client changes that the
interval is more than a month BUT you don't want to
insert a bunch of "months"
*/
by client_id;
if first.client_id then do;
/* no gap possible for first client_id value so output*/
output;
end;
if intck('dtmonth',ldt,segm_date) > 1 then do;
/* this is when to insert*/
/* store the actual value of the current AVG_WEIGHT in a temporary variable*/
tAVG_WEIGHT=AVG_WEIGHT;
/* and same for the current datetime value*/
tdt = SEGM_DATE;
/* set the avg_weight to missing for output*/
call missing(avg_weight);
Segm_date = intnx('dtmonth',ldt,1,'B');
do while (SEGM_DATE lt tdt);
output;
segm_date = intnx('dtmonth',segm_date,1,'B');
end;
/*restore the avg weight*/
AVG_WEIGHT = tAVG_WEIGHT;
output;
end;
else do;
/* when there isn't a gap*/
output;
end;
keep AVG_WEIGHT SEGM_DATE client_id;
run;
Why do we have so many output statements? Likely the data steps you have worked with so far have relied on the implicit OUTPUT at the "bottom" of the data step to write the output. With this approach of writing values that did not exist we NEED an output in the "insert". Once you have a single output statement you need to provide more wherever they are needed.
Do note that I provided some example data with a longer than one month gap. This can be pretty critical as code that works fora single month missing may not work for more.
If you haven't worked with the DO WHILE or DO UNTIL constructs you want to review the logic and requirements for proper exits.
Click image to register for webinar
