BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
phdstudent123
Calcite | Level 5

I run into a problem of filling the missing values with non-missing values. What's tricky here is the nonmissng values are random, so I cannot determine whether to use the first non-missing value or the last non-missing value.

Example: Variable is my variable of interest

ID company variable
1 A 123
1 A . 
1 A 123
1 A 123
1 A .
2 B 321
B .

B .

B .


What i want here is to fill the variable with 123 if the id=1 and variable with 321 if id=2.

 

Any suggestion will be appreciated ! Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
stat_sas
Ammonite | Level 13

Hi,

 

Please try this.

 

proc sql;
create table want as
select ID, company, max(variable) as variable from have
group by id;
quit;

View solution in original post

7 REPLIES 7
phdstudent123
Calcite | Level 5

Forgot to mention that variable is not fixed and can be solely determined by known variables only with the same ID number.

stat_sas
Ammonite | Level 13

Hi,

 

Please try this.

 

proc sql;
create table want as
select ID, company, max(variable) as variable from have
group by id;
quit;

phdstudent123
Calcite | Level 5

Thank you! That works. Btw, does that also apply to string variables too?

 

 

novinosrin
Tourmaline | Level 20

is it that simple to fill with max of values as your sample data suggests?

pau13rown
Lapis Lazuli | Level 10

not sure i understand the data but id remove duplicates then merge back on

 

proc sort data=A;

by id company descending variable;

run;

 

proc sort data=A out=B nodupkey;

by id company;

run;

 

data C;

merge A (drop=variable) B;

by id company;

run;

 

but the other solution suggested is more pithy!

phdstudent123
Calcite | Level 5
Thank you, that works as well!
Patrick
Opal | Level 21

@phdstudent123

Then please accept one of the answers as solution so this question gets closed.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 3004 views
  • 0 likes
  • 5 in conversation