topic Re: How can I get what I want? in SAS Programming

How can I get what I want?

Linlin — Thu, 23 May 2013 20:42:08 GMT

Thank you for your help!

data have;

input id year bmi;

cards;

1 1995 .

1 1996 6

1 1999 9

2 1997 7

2 1998 8

2 1999 .

3 1995 5

3 1998 .

3 1999 9

;

want:

id year bmi

1 1995 6

1 1996 6

1 1999 9

2 1997 7

2 1998 8

2 1999 8

3 1995 5

3 1998 7

3 1999 9

;

Re: How can I get what I want?

Peter_C — Thu, 23 May 2013 20:48:43 GMT

Is it just "read ahead" that you need or are there rules like "never read ahead on first.id"

Re: How can I get what I want?

Reeza — Thu, 23 May 2013 20:58:20 GMT

What was the rule that was used for ID 3 and year 1998? It seems to be an interpolation, but carry forward if it was one year and carried back one year for id 1 and 2?

Re: How can I get what I want?

Haikuo — Fri, 24 May 2013 00:45:38 GMT

Hi LinLin,

Long time! Here is my try, It will fill up the gap regardless how big it is, using the average of most adjacent 'bmi' from two directions:

data have;

input id year bmi;

cards;

1 1995 .

1 1996 6

1 1999 9

2 1997 7

2 1998 8

2 1999 .

3 1994 .

3 1995 5

3 1996 .

3 1997 .

3 1998 .

3 1999 .

3 2000 9

3 2001 .

;

proc sql;

select a.id, a.year, coalesce(a.bmi,mean(pre_bmi,post_bmi)) as bmi from

(select distinct a.*, b.bmi as pre_bmi

from have a

left join have b

on a.id=b.id

and a.year > b.year

and not missing(b.bmi)

group by a.id,a.year

having a.year-b.year=min(a.year-b.year)) a,

(select distinct a.*, b.bmi as post_bmi

from have a

left join have b

on a.id=b.id

and a.year < b.year

and not missing(b.bmi)

group by a.id,a.year

having b.year-a.year=min(b.year-a.year)) b

where a.id=b.id and a.year=b.year;quit;

Hope you the best! It is also doable using Hash(), let me know if you are interested.

Haikuo

Re: How can I get what I want?

PGStats — Fri, 24 May 2013 01:54:43 GMT

For the sake of variety... Using specialized tools :

data have;
input id year bmi;
cards;
1 1995 .
1 1996 6
1 1999 9
2 1997 7
2 1998 8
2 1999 .
3 1994 .
3 1995 5
3 1996 .
3 1997 .
3 1998 .
3 1999 .
3 2000 9
3 2001 .
4 1995 .
4 1996 5
5 1993 .
;

proc reg data=have plots=none noprint;
by id;
model bmi = year;
output out=got p=predBmi;
run;

/* disregard warning and error messages */

data want;
set got;
bmi = coalesce(bmi, round(predBmi), mod(year, 10));
drop predBmi;
run;

proc print; id id year; run;

Message was edited by: PG added MOD function to provide a bmi for single year ids.

Re: How can I get what I want?

Sudhakar_A — Fri, 24 May 2013 05:10:24 GMT

Hi,

This is missing imputation by average of surrounded values. I had this situation like this before for my analysis.

1. sort by year Retain the non - missing values in a new variable

2. Sort descending year create a another new variable with retain values

3. use the Mean function and replace the missing values using the 2 newly created variables

This will work 100% I did it.

Sudhakar

Re: How can I get what I want?

UrvishShah — Fri, 24 May 2013 11:32:56 GMT

Hi,

Based on your sample data provided, i have prepared one SAS Code to achive desire output by using ARRAY as follow:

data have;

input year income;

cards4;

1990 .

1991 2000

1992 3000

1993 .

1994 4000

1995 .

1996 5000

;;;;

proc transpose data = have

out = want

prefix = year_;

id year;

var income;

run;

data want(keep = year income);

set want;

array _miss (*) _numeric_;

do i = 1 to dim(_miss);

if _miss(1) = . then _miss(1) = _miss(1+1);

if _miss(i) = . then _miss(i) = _miss(i-1);

year = substr(vname(_miss(i)),6);

income = _miss(i);

output;

end;

run;

This code will fill the missing values as per its previous value of variable...And if the very first value is missing then it will fill missing value as per its very next observation which is excatly same as your output posted...

But in your output, i still have confusion about how you want to fill missing value for year when its value is 1998...

Request you to please share some rule to handle missing value for year = 1998...

-Urvish

Re: How can I get what I want?

Linlin — Fri, 24 May 2013 12:59:01 GMT

Thank you all for your help!

For now each person only has three observations and one missing value. If first or third one is missing then use the second one, if the second one is missing then use the average of the first and the third. It will be more complicated as more data are added in the future. I will definitely need to try all the solutions.

Haikuo, I have forgotten all the hash methods learned a year ago. Thank you for your good wishes! Happy Memorial Day !

Linlin

Re: How can I get what I want?

Haikuo — Fri, 24 May 2013 13:08:09 GMT

Hi LinLin,

Here is a Hash() approach hopefully to fresh your memory up:

data have;

input id year bmi;

cards;

1 1995 .

1 1996 6

1 1999 9

2 1997 7

2 1998 8

2 1999 .

3 1994 .

3 1995 5

3 1996 .

3 1997 .

3 1998 .

3 1999 .

3 2000 9

3 2001 .

;

data want_hash;

if _n_=1 then do;

if 0 then set have(rename=(id=_id year=_year bmi=_bmi));

declare hash h(dataset:'have(rename=(id=_id year=_year bmi=_bmi))', multidata:'y', ordered:'a');

h.definekey('_id', '_year');

h.definedata(all:'y');

h.definedone();

declare hiter hi('h');

end;

set have;

do rc=hi.setcur(key:id,key:year) by 0 while (rc=0);

rc=hi.prev();

if _id=id then pre_bmi=_bmi;

if _id ne id or not missing(_bmi) then leave;

end;

do rc=hi.setcur(key:id,key:year) by 0 while (rc=0);

rc=hi.next();

if _id=id then post_bmi=_bmi;

if _id ne id or not missing(_bmi) then leave;

end;

bmi=coalesce(bmi,mean(pre_bmi,post_bmi));

keep id year bmi;

run;

Haikuo

Re: How can I get what I want?

Linlin — Fri, 24 May 2013 13:32:31 GMT

Too bad I have to learn Hash now:smileysilly:. Thank you again! - Linlin