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
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;
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;
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.
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;
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;
Thanks Jason, Reeza and Stat.
It worked.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.