BookmarkSubscribeRSS Feed
pravin
Calcite | Level 5

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

aaaaa201201
aaaaa201202
aaaaa201203
aaaaa201204
aaaaa201205
aaaaa201206
aaaaa201207
aaaaa201208
aaaaa201209
aaaaa201210
bbbbb201201
bbbbb201202
bbbbb201203
bbbbb201204
bbbbb201205
bbbbb201206
bbbbb201207
bbbbb201208
bbbbb201209
bbbbb201210
ccccc201201
ccccc201202
ccccc201203
ccccc201204
ccccc201205
ccccc201206
ccccc201207
ccccc201208
ccccc201209
ccccc201210
7 REPLIES 7
Haikuo
Onyx | Level 15

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

pravin
Calcite | Level 5

thanks, how about if i want the year from 201101 to 201210?

art297
Opal | Level 21

What would be your best guess if you had to discover it by yourself?

Haikuo
Onyx | Level 15

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

art297
Opal | Level 21

: Not trying to be difficult, but I like to see posters at least attempt a solution.

Haikuo
Onyx | Level 15

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. Smiley Happy

Haikuo

LinusH
Tourmaline | Level 20

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

Data never sleeps

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!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 894 views
  • 0 likes
  • 4 in conversation