topic Re: Fill empty spaces with the table itself. in SAS Programming

Fill empty spaces with the table itself.

WilliamB — Fri, 06 Sep 2019 06:52:51 GMT

Hello,

I would like to fill the empty spaces of my table when information is already available.
For example for "James", I already had the information "58", so I can fill the empty space.
But I do not want to make a type code;

If name="James" then code="58"

If name="Julien" then code="24"

Because in reality my table 1 is 40000 lines!

Do you have a solution?

Table 1
Family	Name	Code
Herz	James	58
Quez	Sophie	78
Herz	James
Henry	Martin	87
Patric	François
UE	Julien
UE	Julien	24
RE	Sébastien	4


I want this:
Family	Name	Code
Herz	James	58
Quez	Sophie	78
Herz	James	58
Henry	Martin	87
Patric	François
UE	Julien	24
UE	Julien	24
RE	Sébastien	4

Re: Fill empty spaces with the table itself.

PeterClemmensen — Fri, 06 Sep 2019 07:03:05 GMT

data have;
input Family $ Name :$20. Code;
infile datalines missover;
datalines; 
Herz   James     58
Quez   Sophie    78
Herz   James       
Henry  Martin    87
Patric François    
UE     Julien      
UE     Julien    24
RE     Sébastien 4 
;

data want(drop=rc);
    if _N_=1 then do;
        declare hash h(dataset:'have(where=(Code ne .)');
        h.definekey('Name');
        h.definedata('Code');
        h.definedone();
    end;

    set have;

    if code=. then rc=h.find();
run;

Re: Fill empty spaces with the table itself.

Jagadishkatam — Fri, 06 Sep 2019 07:03:51 GMT

Please try the untested code

proc sort data=have;
by family name descending code;
run;

data want;
set have(rename=(code=code2));
by family name;
retain code;
if first.name then code=.;
if code2 ne . then code=code2;
run;

Re: Fill empty spaces with the table itself.

WilliamB — Fri, 06 Sep 2019 08:22:16 GMT

Thank you.

I simplify my table. I wanted to redo your code on my problem but it does not work.

You can help me?

Have :
Emp	Client	Grappe	Cat	Cat_ent
286034	500007	50161	2	1
286034	500158	50161		1
201819	510192	50182	3	1
235507	1045	50215	2	1
235507	512314	50215		1
235507	1442	50347	1	1
235507	614923	50347		1
290064	1753	50459		1
290064	811828	50459	2	1
805591	1805	50476		1




I want this
Emp	Client	Grappe	Cat	Cat_ent
286034	500007	50161	2	1
286034	500158	50161	2	1
201819	510192	50182	3	1
235507	1045	50215	2	1
235507	512314	50215	2	1
235507	1442	50347	1	1
235507	614923	50347	1	1
290064	1753	50459	2	1
290064	811828	50459	2	1
805591	1805	50476		1

data Want(drop=rc);

if _N_=1 then do;

declare hash h(dataset:'Have(where=(Cat is null)');

h.definekey('Grappe');

h.definedata('Cat');

h.definedone();

end;

set Have ;

if Cat='' then rc=h.find();

run;

Re: Fill empty spaces with the table itself.

PeterClemmensen — Fri, 06 Sep 2019 09:20:51 GMT

There you go.. I assume that Cat is a numeric variable. If it is character, then replace the missing . with '' in my code

data have;
input Emp Client Grappe Cat Cat_ent;
infile datalines dlm=',' dsd;
datalines;
286034,500007,50161,2,1
286034,500158,50161,,1
201819,510192,50182,3,1
235507,1045,50215,2,1
235507,512314,50215,,1
235507,1442,50347,1,1
235507,614923,50347,,1
290064,1753,50459,,1
290064,811828,50459,2,1
805591,1805,50476,,1
;

data want(drop=rc);
    if _N_=1 then do;
        declare hash h(dataset:"have(where=(Cat ne .)");
        h.definekey('Grappe');
        h.definedata('Cat');
        h.definedone();
    end;

    set have;

    if Cat=. then rc=h.find();
run;