Prev: SAS macro variable
Next: Very High Odds Ratios from proc logistic
From: subbu on 25 May 2010 11:00
I have six variables like

id region code month nrx trx

101 r1 s1 200903 12 24
101 r1 s1 200904 12 27
101 r1 s1 200905 10 26
101 r1 s1 200906 10 22
101 r1 s1 200908 12 24
101 r1 s1 2009010 12 24
101 r1 s1 2009011 12 24
101 r1 s1 2009012 12 24
101 r1 s1 200812 12 24
101 r1 s1 200811 12 23
101 r1 s1 200810 12 26
101 r1 s1 200809 14 40
101 r1 s1 200807 16 24
101 r1 s1 200801 12 24

here some months are missing for example 200907,200909 missed in the
2009 and in the same way for 2008
some months like 200808,200806-200802.

we need for missing months, we need previous month data up to code
variable,
we have insert the rows for missing months and last two variables
we have to assign missing values for missing month.

I need output like this below.

101 r1 s1 200903 12 24
101 r1 s1 200904 12 27
101 r1 s1 200905 10 26
101 r1 s1 200906 10 22
101 r1 s1 200907 . .
101 r1 s1 200908 12 24
101 r1 s1 200909 . .
101 r1 s1 2009010 12 24
101 r1 s1 2009011 12 24
101 r1 s1 2009012 12 24

In the same way for 2008 also.

Thanks,
Subbu.
From: Reeza on 25 May 2010 14:27
On May 25, 8:00 am, subbu <subbus...(a)gmail.com> wrote:
> I have six variables like
>
> id  region code month nrx trx
>
> 101 r1 s1 200903 12 24
> 101 r1 s1 200904 12 27
> 101 r1 s1 200905 10 26
> 101 r1 s1 200906 10 22
> 101 r1 s1 200908 12 24
> 101 r1 s1 2009010 12 24
> 101 r1 s1 2009011 12 24
> 101 r1 s1 2009012 12 24
> 101 r1 s1 200812 12 24
> 101 r1 s1 200811 12 23
> 101 r1 s1 200810 12 26
> 101 r1 s1 200809 14 40
> 101 r1 s1 200807 16 24
> 101 r1 s1 200801 12 24
>
> here some months are missing for example 200907,200909 missed in the
> 2009 and in the same way for 2008
> some months like 200808,200806-200802.
>
> we need for missing months, we need previous month data up to code
> variable,
> we have insert the rows for missing months and last two variables
> we have to assign missing values for missing month.
>
> I need output like this below.
>
> 101 r1 s1 200903 12 24
> 101 r1 s1 200904 12 27
> 101 r1 s1 200905 10 26
> 101 r1 s1 200906 10 22
> 101 r1 s1 200907 .  .
> 101 r1 s1 200908 12 24
> 101 r1 s1 200909 .  .
> 101 r1 s1 2009010 12 24
> 101 r1 s1 2009011 12 24
> 101 r1 s1 2009012 12 24
>
> In the same way for 2008 also.
>
> Thanks,
> Subbu.

create a month table with the fields for each month you need...

ie 200901
200902
200903
.....
200912
200801
200802

You can either do this manually or with a data step using intx
commands for a month.

Then cross join this table with the table above. That will provide
you all the months required.

The ID can be filled in with a retain statement.

OR

Sort your data
Go through data line by line check if month is the next month (use
intx function). If it isn't output the blank record until it is the
record you need.


HTH,
Reeza

eg untested

proc sort data=have;
by month;

data want;
set have;
retain month_check 200801;
do while month ne month_check;
month_check=intx(m, month_check, 1); *check syntax;
output;
end;
output;
run;



 | 
Pages: 1
Prev: SAS macro variable
Next: Very High Odds Ratios from proc logistic