topic Re: Completing missing values in SAS Procedures

Completing missing values

jonatan_velarde — Mon, 07 Dec 2015 17:39:19 GMT

Good day SAS friends:

I would like to be helped to complete this routine in SAS. The data i have is as follows:

Data have:

Input Sample age_in_days measure;

cards;

1 10 3.6

1 15 4.0

1 20 4.2

1 25 5.0

1 30 5.5

2 3 3.6

2 4 4.0

2 5 4.2

2 10 5.0

2 25 5.5

;

I need this result:

Sample age_in_days measure

1 3 .

1 4 .

1 5 .

1 10 3.6

1 15 4.0

1 20 4.2

1 25 5.0

1 30 5.5

2 3 3.6

2 4 4.0

2 5 4.2

2 10 5.0

2 15 .

2 20 .

2 25 5.5

2 30 .

as you can see, i need to complete the missing values in the list, lateron i will use to estimate this values using non linear regression.

Thank you very much

Re: Completing missing values

Haikuo — Mon, 07 Dec 2015 18:47:00 GMT

The easiest way I see is to TRANSPOSE 2 times, back and forth.

options validvarname=any;

Data have;
	Input Sample age_in_days measure;
	cards;
1   10   3.6
1   15   4.0
1   20   4.2
1   25   5.0
1   30   5.5 
2     3   3.6
2     4   4.0
2     5   4.2
2   10   5.0
2   25   5.5
;

/*filling the blanks*/
proc transpose data= have out=h1;
	by sample;
	var measure;
	id age_in_days;
run;

/*back to orginal structure, here you need however to check the 
variable order first, then use it in the downstream transpose*/
PROC TRANSPOSE DATA=h1
	OUT=h2

	NAME=age_in_days
	LABEL=Label
;
	BY Sample;
	id _name_;
	VAR "10"n -- "5"n;
RUN;

QUIT;

/*restore age back to numeric*/
data want;
	set h2 (rename=age_in_days=age);
	age_in_days=input(age,best.);
	drop age;
run;

Re: Completing missing values

FreelanceReinh — Mon, 07 Dec 2015 22:50:38 GMT

PROC SQL might be another alternative:

proc sql;
create table want as
select *
from (select distinct a.sample, b.age_in_days from have a, have b)
     natural full join have;
quit;

Re: Completing missing values

jonatan_velarde — Tue, 08 Dec 2015 14:14:58 GMT

Hi There:

I have accepted your answer as solution becaus i believe your code is perfect to the example i propose in muy question, now i need to apply your answer to my original data base, i work in growing models and i would like to offer an real example:

here i go:

data grow;

input City Subject Region City Town Sex FreqGE day month year age age_in_days weight weight_in_Kilograms;

cards;
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   Initial_age   0   Initial_weight   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age1   2240   weight1   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age2   2282   weight2   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age3   2343   weight3   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age4   2373   weight4   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age5   2400   weight5   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age6   2428   weight6   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age7   2457   weight7   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age8   2483   weight8   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age9   2519   weight9   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age10   2540   weight10   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age11   2568   weight11   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age12   2596   weight12   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age13   2624   weight13   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age14   2653   weight14   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age15   2680   weight15   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age16   2708   weight16   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age17   2737   weight17   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age18   2764   weight18   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age19   2793   weight19   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age20   2821   weight20   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age21   2849   weight21   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age22   2876   weight22   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age23   2905   weight23   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age24   2932   weight24   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age25   2960   weight25   119
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age26   2989   weight26   118.8
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age27   3016   weight27   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age28   3045   weight28   112.8
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age29   3072   weight29   108.8
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age30   3100   weight30   106.6
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age31   3129   weight31   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age32   3156   weight32   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age33   3191   weight33   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age34   3219   weight34   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age35   3247   weight35   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age36   3303   weight36   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age37   3331   weight37   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age38   3360   weight38   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age39   3387   weight39   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age40   3415   weight40   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age41   3443   weight41   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age42   3471   weight42   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age43   3500   weight43   105.8
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age44   3527   weight44   107.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age45   3555   weight45   108.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age46   3583   weight46   106.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age47   3611   weight47   106.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age48   3639   weight48   101.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age49   3661   weight49   107.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age50   3694   weight50   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age51   3723   weight51   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age52   3751   weight52   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age53   3779   weight53   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age54   3807   weight54   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age55   3835   weight55   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age56   3863   weight56   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age57   3891   weight57   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age58   3919   weight58   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age59   3947   weight59   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age60   3975   weight60   95.1
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age61   4003   weight61   99
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age62   4031   weight62   104.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age63   4059   weight63   107.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age64   4227   weight64   93.6
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age65   4255   weight65   101
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age66   4283   weight66   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age67   4311   weight67   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age68   4339   weight68   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age69   4367   weight69   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age70   4395   weight70   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age71   4423   weight71   100.5
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age72   4451   weight72   100.1
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age73   4479   weight73   85.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age74   4507   weight74   88
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age75   4535   weight75   86
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age76   4563   weight76   91.5
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age77   4591   weight77   90
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age78   4619   weight78   90.5
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age79   4647   weight79   88.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age80   4675   weight80   92.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age81   4871   weight81   95.9
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age82   4899   weight82   92.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age83   4927   weight83   90.7
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age84   4955   weight84   88.8
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age85   4983   weight85   86.9
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age86   5011   weight86   88.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age87   5039   weight87   91.5
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age88   5067   weight88   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age89   5095   weight89   88.6
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age90   5123   weight90   88.3
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age91   5152   weight91   87.7
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age92   5180   weight92   80.5
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age93   5235   weight93   75
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age94   5263   weight94   74.7
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age95   5297   weight95   78.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age96   5319   weight96   80.6
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age97   5347   weight97   79.5
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age98   5375   weight98   83.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age99   5431   weight99   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age100   5459   weight100   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age101   5487   weight101   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age102   5515   weight102   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age103   5571   weight103   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age104   5599   weight104   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age105   5627   weight105   .
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age106   5655   weight106   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age107   5683   weight107   87.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age108   5711   weight108   80.6
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age109   5742   weight109   79.5
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age110   5767   weight110   83.4
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age111   5795   weight111   84.2
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age112   5824   weight112   87.3
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age113   5851   weight113   86.8
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age114   5879   weight114   88.7
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age115   5907   weight115   90.6
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age116   5935   weight116   84.8
1   81   CABRA_58   BITU_01   2   M   .   2   25   1999   age117   5963   weight117   82.3
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   Initial_age   0   Initial_weight   .

2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age16   1880   weight16   82
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age17   1909   weight17   85.4
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age18   1936   weight18   90
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age19   1965   weight19   89.3
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age20   1993   weight20   93.8
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age21   2021   weight21   92.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age22   2048   weight22   97.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age23   2077   weight23   92
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age24   2104   weight24   99
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age25   2132   weight25   90
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age26   2161   weight26   98
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age27   2188   weight27   102
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age28   2217   weight28   105
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age29   2244   weight29   102
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age30   2272   weight30   97.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age31   2301   weight31   106
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age32   2328   weight32   107
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age33   2363   weight33   108.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age34   2391   weight34   107
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age35   2419   weight35   110
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age36   2475   weight36   95.3
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age37   2503   weight37   83.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age38   2532   weight38   83
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age39   2559   weight39   82.1
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age40   2587   weight40   87
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age41   2615   weight41   85
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age42   2643   weight42   78
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age43   2672   weight43   82.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age44   2699   weight44   79.4
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age45   2727   weight45   80
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age46   2755   weight46   77.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age47   2783   weight47   .
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age48   2811   weight48   79.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age49   2833   weight49   81.2
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age50   2866   weight50   77.4
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age51   2895   weight51   75.8
2   533   CABRA_344   CABRA_297   .   M   .   6   2   2001   age52   2923   weight52   66.2
;

In here i need to complete the mising age_in_days.

Thank You

Re: Completing missing values

FreelanceReinh — Tue, 08 Dec 2015 15:32:14 GMT

Maybe something like this will meet your requirements:

proc sql;
create table temp as
select *
from (select distinct a.subject, a.city, a.region, a.city2, a.town, a.sex, a.freqGE,
                      a.month, a.day, a.year, b.age_in_days from grow a, grow b)
     natural full join grow
order by subject, age_in_days;
quit;

data want;
set temp;
by subject age_in_days;
if first.subject then i=0;
else i+1;
if i then do;
  age=cats('age',i);
  weight=cats('weight',i);
end;
drop i;
run;

Assumptions:

Variable Subject is the primary key and City, Region, City2 (renamed from your second variable named City), Town, Sex, FreqGE (which is missing in all observations), day, month (or rather month, day -- in view of values 2, 25) and year depend only on Subject.
The aim is to create all combinations of subjects with age_in_days values and to keep the pertinent values of weight_in_Kilograms.
Variables age and weight are to be overwritten with values 'age1', 'age2', ... and 'weight1', 'weight2', ..., respectively, reflecting the new ranks of the ordered positive age_in_days values per subject. In the first observation per subject, which has age_in_days=0, the special values age='Initial_age', weight='Initial_weight' are to be maintained.

Re: Completing missing values

jonatan_velarde — Wed, 09 Dec 2015 14:44:56 GMT

Hi there:

Its awesome the way to make this program so usefull, is the solutin, but unfortunally my PC dont accept the quantity of data created,

the SAS log says insufficient space to obtain result.

Thanks you very much