BookmarkSubscribeRSS Feed
waldo11
Fluorite | Level 6

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?

 

 

 

5 REPLIES 5
ballardw
Super User

@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
Fluorite | Level 6

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
Super User

@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
Super User Tom
Super User

What is a "self-updating table" ?

Are you trying to create a view?

Ksharp
Super User
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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 930 views
  • 0 likes
  • 4 in conversation