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

hi guys,

i need your help on this.

Here is my input file attached and i need to get the same output plus the missing values retained from the previous observations but my retain statement doesn't seem to be working .Anyone knows why?

Thanks,

data one;
infile 'C:\Users\Trajce\Contacts\Desktop\test.txt';
input id $3. gender $1. dob mmddyy8. visit 1. h3 3. sbp 3. dbp 3.;
retain gender dob ;
format dob mmddyy8.;

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

This should suffice.  If you have more variables there is and easier way where you don't have to refer to any of the variables by name.

data one;
   *infile 'C:\Users\Trajce\Contacts\Desktop\test.txt';
  
input id $3. _gender $1. _dob mmddyy8. visit 1. h3 3. sbp 3. dbp 3.;
  
retain gender dob;
   length gender $1 dob 8;
   gender = coalesceC(_gender,gender);
   dob    = coalesce(_dob,dob);
  
format dob mmddyy8.;
  
drop _:;
   cards;
001M10/21/461080140080
001         2082142084
001         3078138078
002F11/17/221066120070
003F04/04/181084150090
003         2088152102
004M12/21/101072120074
004         2070122076
004         3078128078
005F08/02/311092180110
006         1076180112
006         2080178090
;;;;
   run;

View solution in original post

7 REPLIES 7
data_null__
Jade | Level 19

This should suffice.  If you have more variables there is and easier way where you don't have to refer to any of the variables by name.

data one;
   *infile 'C:\Users\Trajce\Contacts\Desktop\test.txt';
  
input id $3. _gender $1. _dob mmddyy8. visit 1. h3 3. sbp 3. dbp 3.;
  
retain gender dob;
   length gender $1 dob 8;
   gender = coalesceC(_gender,gender);
   dob    = coalesce(_dob,dob);
  
format dob mmddyy8.;
  
drop _:;
   cards;
001M10/21/461080140080
001         2082142084
001         3078138078
002F11/17/221066120070
003F04/04/181084150090
003         2088152102
004M12/21/101072120074
004         2070122076
004         3078128078
005F08/02/311092180110
006         1076180112
006         2080178090
;;;;
   run;
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

thanks Data_Null_

so there was no way the retain statement could retain those values by itself,w/o calling the coalesce f-ion?

data_null__
Jade | Level 19

In your program the values of GENDER and DOB are retained.  BUT when the input statement is executed and the field is blank they are set to missing.

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

i am confused now.

I thought retain values never got set to missing.But lets consider the second observation where _gender is missing then gender gets set to missing too?

gender = coalesceC(_gender,gender);


Then how at the end gender gets the value if both the arguments are missing?

data_null__
Jade | Level 19

I was talking about GENDER and DOB in YOUR program.  Even though they are RETAINED when the INPUT state executes the value of GENDER and DOB are replaced with what is read.  On the second obs that is missing.

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

thank you.

And how can  i fix the issue with the id=006 observations?

the gender and the date are missing in the first record with id=006 which means the second one should have missing fields too but that's not the case.I tried with if first.id and _gender= '  '  but was not successful

data_null__
Jade | Level 19

You will need to move the coalesce bit to another data step so you can use SET with BY ID.

Or to make in easier in some respect use the UPDATE statement this will fix the issue of DOB and GENDER being carried over from  the previous ID.  I does however carry forward ALL variables within each ID, which may not be what you want for all variables.  You don't have any other missings but I've included the code to reset them as an example.

data one;
   *infile 'C:\Users\Trajce\Contacts\Desktop\test.txt';
  
input id $3. gender $1. dob mmddyy8. visit 1. h3 3. sbp 3. dbp 3.;
  
format dob mmddyy8.;
  
cards;
001M10/21/461080140080
001         2082142084
001         3078138078
002F11/17/221066120070
003F04/04/181084150090
003         2088152102
004M12/21/101072120074
004         2070122076
004         3078128078
005F08/02/311092180110
006         1076180112
006         2080178090
;;;;
   run;
data filled;
   update one(obs=1) one;
   by id;
   output;
  
call missing(of visit--dbp); *to insure these are not carried forward;
  
run;
  

Message was edited by: data _null_

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 915 views
  • 4 likes
  • 2 in conversation