Creating a person-time data set

[pronabesh]

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

[Reeza]

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;

[pronabesh]

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.

[stat_sas]

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;

[JasonAllen · Accepted solution]

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;

[pronabesh]

Thanks Jason, Reeza and Stat.

It worked.