topic Re: Calculate a column that returns the number of months in SAS Data Management

Calculate a column that returns the number of months

InêsMaximiano — Thu, 21 Jul 2022 16:21:41 GMT

data have;
input UserID Year Month Flag;
datalines;

UserID	Year	Month	Flag
1	2021	8	0
1	2021	9	1
1	2022	1	1
1	2022	2	0
1	2022	3	1
2	2020	1	1
2	2020	2	0
2	2020	3	1
2	2020	7	0
3	2022	1	0
3	2022	2	1
3	2022	3	0
3	2022	4	0
3	2022	5	1

;

I want to create a new column that returns 0 if Flag = 1 and that for the other rows it returns the number of months that have passed since the last time that Flag was 1.

Like in the following exemple:

UserID	Year	Month	Flag	Column wanted
1	2021	8	0	.
1	2021	9	1	0
1	2022	1	1	0
1	2022	2	0	1
1	2022	3	1	0
2	2020	1	1	0
2	2020	2	0	1
2	2020	3	1	0
2	2020	7	0	4
3	2022	1	0	.
3	2022	2	1	0
3	2022	3	0	1
3	2022	4	0	2
3	2022	5	1	0

Re: Calculate a column that returns the number of months

ballardw — Thu, 21 Jul 2022 16:57:04 GMT

One way:

data have;
   input UserID	Year	Month	Flag;
datalines;
1	2021	8	0
1	2021	9	1
1	2022	1	1
1	2022	2	0
1	2022	3	1
2	2020	1	1
2	2020	2	0
2	2020	3	1
2	2020	7	0
3	2022	1	0
3	2022	2	1
3	2022	3	0
3	2022	4	0
3	2022	5	1
;

data want;
   set have;
   by userid;
   retain flagdate;
   if first.userid then do;
       wanted=.;
       flagdate=.;
   end;
   if flag=1 then do;
      wanted=0;
      flagdate=mdy(month,1,year);
   end;
   else do;
      date=mdy(month,1,year);
      if flagdate then wanted= intck('month',flagdate,date);
   end;
   drop flagdate date;
run;

By group processing creates automatic variables First and Last for each variable on the by statement indicating if the current value is first or last value of a group and can be used to reset values as shown.

Retain keeps values of a variable across data step boundaries for later use.

MDY function to create actual date values for use with the INTCK function which returns counts of intervals.

Note use of data step to provide example data. Please try to do that and paste into a text box opened on the forum with the </> icon. Other text may end up with content issues depending on source and paste behavior of the forum.

Re: Calculate a column that returns the number of months

InêsMaximiano — Fri, 22 Jul 2022 09:24:55 GMT

Thank you very much! That really helped!