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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: