topic Re: Macro Loop - Create Tables with Months in SAS Programming

Macro Loop - Create Tables with Months

waldo11 — Tue, 03 Sep 2019 18:41:00 GMT

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?

Re: Macro Loop - Create Tables with Months

ballardw — Tue, 03 Sep 2019 19:14:03 GMT

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

Re: Macro Loop - Create Tables with Months

waldo11 — Tue, 03 Sep 2019 19:24:33 GMT

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

Re: Macro Loop - Create Tables with Months

Tom — Tue, 03 Sep 2019 21:40:01 GMT

What is a "self-updating table" ?

Are you trying to create a view?

Re: Macro Loop - Create Tables with Months

ballardw — Tue, 03 Sep 2019 22:09:13 GMT

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

Re: Macro Loop - Create Tables with Months

Ksharp — Wed, 04 Sep 2019 12:21:33 GMT

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;