I have data below, customer id and the mth from 201201 to 201210 but some customers got missing mth.
I want the output to be each customer have 10 rows from 201201 to 201210.
Any advices how to handle this please? Thanks.
data have;
input id $ yymm $;
cards;
aaaaa 201201
aaaaa 201202
aaaaa 201204
aaaaa 201205
aaaaa 201207
bbbbb 201201
bbbbb 201203
bbbbb 201206
ccccc .
;
run;
output---->
aaaaa | 201201 |
aaaaa | 201202 |
aaaaa | 201203 |
aaaaa | 201204 |
aaaaa | 201205 |
aaaaa | 201206 |
aaaaa | 201207 |
aaaaa | 201208 |
aaaaa | 201209 |
aaaaa | 201210 |
bbbbb | 201201 |
bbbbb | 201202 |
bbbbb | 201203 |
bbbbb | 201204 |
bbbbb | 201205 |
bbbbb | 201206 |
bbbbb | 201207 |
bbbbb | 201208 |
bbbbb | 201209 |
bbbbb | 201210 |
ccccc | 201201 |
ccccc | 201202 |
ccccc | 201203 |
ccccc | 201204 |
ccccc | 201205 |
ccccc | 201206 |
ccccc | 201207 |
ccccc | 201208 |
ccccc | 201209 |
ccccc | 201210 |
There will be many fancy ways to do the same job, if your request is just as simple as being presented, the simplest approach I can think of is the following:
data have;
input id $ yymm $;
cards;
aaaaa 201201
aaaaa 201202
aaaaa 201204
aaaaa 201205
aaaaa 201207
bbbbb 201201
bbbbb 201203
bbbbb 201206
ccccc .
;
run;
data h1;
set have(keep=id);
by id notsorted;
if first.id;run;
data want;
set h1;
do _yymm=201201 to 201210;
yymm=put(_yymm,6.);
output;
end;
drop _:;
run;
proc print;run;
Haikuo
thanks, how about if i want the year from 201101 to 201210?
What would be your best guess if you had to discover it by yourself?
Come on, Art, Give OP a break. I think this would be a little harder for OP, either you want to go ugly doing two loops ( do 201101 to 201112; do 201201 to 201210); or you need to involve those time interval functions. So here is the latter approach:
data want;
set h1;
do n=0 to intck('month',input('201101',yymmn6.) ,input('201210',yymmn6.));
_yymm=intnx('month',input('201101',yymmn6.),n);
yymm=put(_yymm, yymmn6.);
output;
end;
drop _:;
run;
Haikuo
Art, there is no doubt that you are THE nicest guy on the forum. That is why I 'd like messing with you once for a while.
Haikuo
Select distinct customer_id
The data step with a do-loop, that outputs one row per year and Customer_id.
And then outer join back with original data (if it has some more columns you needed).
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.