SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Creating a person-time data set

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Creating a person-time data set

Hi SAS Community,

I have two data sets (1) with a list of user IDs (see table 1 below) and (2) with a list of dates (see table 2 below). I want to create a person-time data set from the two tables (see table 3 example below).

Table 1

User_ID

1

2

3

4

Table 2

Date

23JUN2014

24JUN2014

25JUN2014

26JUN2014

Table 3

User_ID Date

1  23JUN2014

1  24JUN2014

1  25JUN2014

1  26JUN2014

2  23JUN2014

2  24JUN2014

2  25JUN2014

2  26JUN2014

3  23JUN2014

3  24JUN2014

3  25JUN2014

3  26JUN2014

4  23JUN2014

4  24JUN2014

4  25JUN2014

4  26JUN2014

Any help will be much appreciated.

Best,

Pronabesh


Accepted Solutions
Solution
‎07-11-2014 03:58 PM
SAS Employee
Posts: 15

Re: Creating a person-time data set

Hello Pronabesh,

Maybe try creating a Cartesian product join using PROC SQL:

data have1;

  input

    user_id;

datalines;

1

2

3

4

;

run;

data have2;

  length

    cdate $9;

  format

    date date9.;

  input

    cdate $;

  date=input(cdate,date9.);

  drop

    cdate;

datalines;

23JUN2014

24JUN2014

25JUN2014

26JUN2014

;

run;

proc sql;

  create table want as

    select * from have1, have2;

quit;

View solution in original post


All Replies
Super User
Posts: 17,829

Re: Creating a person-time data set

Are you sure output 3 is what you want? I would have assumed a person - time dataset was

1  23JUN2014

1  24JUN2014

1  25JUN2014

1  26JUN2014

2  23JUN2014

2  24JUN2014

2  25JUN2014

2  26JUN2014

etc.

If so, then use a cross join in SQL.

proc sql;

create table want as

select a.*, b.*

from a, b;

quit;

Contributor
Posts: 58

Re: Creating a person-time data set

Reeza,

Apologies for the error. As you rightly mentioned person-toime data set should be

1  23JUN2014

1  24JUN2014

1  25JUN2014

1  26JUN2014

2  23JUN2014

2  24JUN2014

2  25JUN2014

2  26JUN2014

I corrected my original question.

Trusted Advisor
Posts: 1,204

Re: Creating a person-time data set

Try this,

data table1;
input User_ID;
datalines;
1
2
3
4
;

data Table2;
input date date9.;
format date date9.;
n=_n_;
datalines;
23JUN2014
24JUN2014
25JUN2014
26JUN2014
;

proc sql;
create table table3(drop=user_id rename=(n=user_id)) as
select from table1,table2
order by n;
quit;

Solution
‎07-11-2014 03:58 PM
SAS Employee
Posts: 15

Re: Creating a person-time data set

Hello Pronabesh,

Maybe try creating a Cartesian product join using PROC SQL:

data have1;

  input

    user_id;

datalines;

1

2

3

4

;

run;

data have2;

  length

    cdate $9;

  format

    date date9.;

  input

    cdate $;

  date=input(cdate,date9.);

  drop

    cdate;

datalines;

23JUN2014

24JUN2014

25JUN2014

26JUN2014

;

run;

proc sql;

  create table want as

    select * from have1, have2;

quit;

Contributor
Posts: 58

Re: Creating a person-time data set

Thanks Jason, Reeza and Stat.

It worked.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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