topic Re: Creating a person-time data set in SAS Data Management

Creating a person-time data set

pronabesh — Fri, 11 Jul 2014 18:01:10 GMT

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

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

Re: Creating a person-time data set

Reeza — Fri, 11 Jul 2014 18:32:31 GMT

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;

Re: Creating a person-time data set

stat_sas — Fri, 11 Jul 2014 18:35:39 GMT

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;

Re: Creating a person-time data set

pronabesh — Fri, 11 Jul 2014 18:41:30 GMT

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.

Re: Creating a person-time data set

JasonAllen — Fri, 11 Jul 2014 19:58:03 GMT

Hello Pronabesh,

Maybe try creating a Cartesian product join using PROC SQL:

data have1;

input

user_id;

datalines;

;

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;

Re: Creating a person-time data set

pronabesh — Fri, 11 Jul 2014 20:32:24 GMT

Thanks Jason, Reeza and Stat.

It worked.