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 |
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.