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).
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!
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.