Macro Loop - Create Tables with Months

[waldo11]

I have a table that has the following information. Let's call this table "Source".

User   Activation Date 

1           01JAN2018

2           23FEB2017

3           11JAN2017

4           23JAN2019

I'm trying to create a macro that creates a table that keeps updating itself in the following format.

YYYYMM User Flag

201701       1      0

.                  2      0

.                  3      1

.                  4      0

201702       1      0

.                  2      1

.                  3      0

.                  4      0

201801       1      1

.                  2      0

.                  3      0

.                  4      0

201901       1      0

.                  2      0

.                  3      0

.                  4      1

The Flag field denotes whether the activation date was in the newly created YYYYMM field. How do I use the source table to have a self-updating table?

[ballardw]

---

@waldo11 wrote:

I have a table that has the following information. Let's call this table "Source".

User   Activation Date 

1           01JAN2018

2           23FEB2017

3           11JAN2017

4           23JAN2019

I'm trying to create a macro that creates a table that keeps updating itself in the following format.

YYYYMM User Flag

201701       1      0

.                  2      0

.                  3      1

.                  4      0

201702       1      0

.                  2      1

.                  3      0

.                  4      0

201801       1      1

.                  2      0

.                  3      0

.                  4      0

201901       1      0

.                  2      0

.                  3      0

.                  4      1

The Flag field denotes whether the activation date was in the newly created YYYYMM field. How do I use the source table to have a self-updating table?

---

First thing, is your date a SAS date variable, i.e. a numeric variable with format such as date9 applied? Anything manipulating dates should start with actual SAS date values.

Second, how exactly do you intend to use your target data set? It may well be that what you want does not require anything new at all.

Generally that sort of structure with a very important grouping variable actually missing on most records is very poor to have in data. Creating a report similar to that is no problem but when you have missing values then keeping things together is likely to be awkward at best and perhaps even extremely difficult to work.

[waldo11]

The Activation Date field is a date field. I intend to use the target dataset to create a new dataset with the following structure:

User  Activation_201701 Activation_201702.....Activation_201801..... Activation_201901

1                    0                             0                               1                                    0

2                    0                             1                               0                                    0

3                    1                             0                               0                                    0

4                    0                             0                               0                                    1

[ballardw]

---

@waldo11 wrote:

The Activation Date field is a date field. I intend to use the target dataset to create a new dataset with the following structure:

User  Activation_201701 Activation_201702.....Activation_201801..... Activation_201901

1                    0                             0                               1                                    0

2                    0                             1                               0                                    0

3                    1                             0                               0                                    0

4                    0                             0                               0                                    1

---

Why?

Most processes in SAS will work better with the "long" format, one record per user and date, than "wide" with multiple variables like that.

If a Person wants to see stuff like that then wait until the end and make a report using the date variable in an across role (in Proc Report) or a class variable in Proc tabulate as a column variable.

[Tom]

What is a "self-updating table" ?

Are you trying to create a view?

[Ksharp]

data have;
input _User    Date 	: date9.;
format date yymmn6.;
cards;
1           01JAN2018
2           23FEB2017
3           11JAN2017
4           23JAN2019
;

proc sql;
create table want as
select a.*,ifn(missing(b.date),0,1) as flag
 from (
  select * from (select distinct _user from have),(select distinct date from have)
) as a left join have as b
on a._user=b._user and a.date=b.date
order by date,_user;
quit;