BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
pronabesh
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
JasonAllen
SAS Employee

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

5 REPLIES 5
Reeza
Super User

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

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
Ammonite | Level 13

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
SAS Employee

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

Thanks Jason, Reeza and Stat.

It worked.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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