topic Re: how to fill in the missing rows? in SAS Procedures

how to fill in the missing rows?

pravin — Wed, 14 Nov 2012 13:17:36 GMT

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

Re: how to fill in the missing rows?

Haikuo — Wed, 14 Nov 2012 13:53:01 GMT

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

Re: how to fill in the missing rows?

LinusH — Wed, 14 Nov 2012 13:53:47 GMT

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

Re: how to fill in the missing rows?

pravin — Wed, 14 Nov 2012 14:15:32 GMT

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

Re: how to fill in the missing rows?

art297 — Wed, 14 Nov 2012 14:22:18 GMT

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

Re: how to fill in the missing rows?

Haikuo — Wed, 14 Nov 2012 15:36:43 GMT

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

Re: how to fill in the missing rows?

art297 — Wed, 14 Nov 2012 15:39:32 GMT

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

Re: how to fill in the missing rows?

Haikuo — Wed, 14 Nov 2012 15:54:13 GMT

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