Help using Base SAS procedures

how to fill in the missing rows?

Reply
New Contributor
Posts: 2

how to fill in the missing rows?

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
Respected Advisor
Posts: 3,156

Re: how to fill in the missing rows?

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

New Contributor
Posts: 2

Re: how to fill in the missing rows?

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

PROC Star
Posts: 7,492

Re: how to fill in the missing rows?

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

Respected Advisor
Posts: 3,156

Re: how to fill in the missing rows?

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

PROC Star
Posts: 7,492

Re: how to fill in the missing rows?

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

Respected Advisor
Posts: 3,156

Re: how to fill in the missing rows?

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

Super User
Posts: 5,437

Re: how to fill in the missing rows?

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
Ask a Question
Discussion stats
  • 7 replies
  • 259 views
  • 0 likes
  • 4 in conversation