topic Re: Conditional Input from Raw Data in SAS Programming

Conditional Input from Raw Data

jfaruqui — Fri, 16 Nov 2018 10:58:50 GMT

I have a raw data file that needs to be read into a dataset.

it looks like this:

----+----1----+----2----+----3----+----4----+----5

Harry Mary 123 Vermont Ave.
Joe 3
Lisa 2
Felix Laura 6 Baltic Drive
Karen 12
Thomas Felicia 424 Suffolk place
Edward Bella 666 Vampire Lane
Jeff 9
Jared 7

Tim 11

The variables are Father, Mother, Address followed by Child (if any) and Child Age.

I need to create a dataset that will have one observation per child with repeating Father, Mother and Address information. If no children then Child and age value should be blank. Please Help .. Thanks

Desired Result would look like this:

Father Mother Address Child Age

---------------------------------------------------

Harry Mary 123 Vermont Ave. Joe 3
Harry Mary 123 Vermont Ave. Lisa 2
Felix Laura 6 Baltic Drive Karen 12
Thomas Felicia 424 Suffolk place .
Edward Bella 666 Vampire Lane Jeff 9
Edward Bella 666 Vampire Lane Jared 7
Edward Bella 666 Vampire Lane Tim 11

Re: Conditional Input from Raw Data

PeterClemmensen — Thu, 15 Nov 2018 23:45:03 GMT

Is this a .txt file?

Re: Conditional Input from Raw Data

jfaruqui — Thu, 15 Nov 2018 23:51:01 GMT

yeah ... its a dat file ... i put the ruler up there just for convenience .. its not part of the file

Re: Conditional Input from Raw Data

Shmuel — Fri, 16 Nov 2018 10:11:35 GMT

Assuming you have no delimiters between variables and

each name contains one string only without blanks embedded

then try next code:

data test;
   length father $10 mother $10 address $40; /* addapt length to max expected */
   retain father mother address no_of_childs;
   infile datalines;
   input a_line $80.
   
   /* check is there an address */
   if scan(a_line,3) ne ' ' then do;
      if _N_ > 1 then do;   
	     if no_of_childs = 0 then do;
		    child= ' ';
			child_age = .;
			output;
	     end;
         a_line = compbl(a_line);
         father = scan(a_line,1);
	     mother = scan(a_line,2);
	     add_start = index(a_line, scan(a_line, 3));
	     address = substr(a_line, add_start);
	     no_of_childs = 0;
	  end;
   end;
   else do;
      input child $ child_age;
	  output;
	  no_of_childs +1;
   end;
datalines;
Harry    Mary      123 Vermont Ave.
Joe      3
Lisa     2
Felix    Laura     6 Baltic Drive
Karen    12
Thomas   Felicia   424 Suffolk place
Edward   Bella     666 Vampire Lane
Jeff     9
Jared    7
Tim      11
;
run;

Re: Conditional Input from Raw Data

jfaruqui — Fri, 16 Nov 2018 11:02:04 GMT

Hi Shmuel

I ran your program verbatim (had to add the ";" for first input statement) ... sorry, but not getting the required result.

I have edited the initial question text to include the desired result ..

Re: Conditional Input from Raw Data

s_lassen — Fri, 16 Nov 2018 11:26:41 GMT

How about this?

data want;                                                                                                                              
  infile cards truncover eof=done;                                                                                                      
  input @1 father $9. mother $10. address $40.;                                                                                         
  do _N_=1 by 1 until(0);                                                                                                               
    input child $9. age ?? 3.0 @@;                                                                                                      
    if missing(age) then leave;                                                                                                         
    output;                                                                                                                             
    input;                                                                                                                              
    end;                                                                                                                                
  done: /* This is to avoid the "missing card" message in log */                                                                        
  if _N_=1 then do;  /* output when no children read */                                                                                 
    call missing(Child);  /* Father variable has been read as Child */                                                                                                              
    output;                                                                                                                             
    end;                                                                                                                                
cards;                                                                                                                                  
Harry    Mary      123 Vermont Ave.                                                                                                     
Joe      3                                                                                                                              
Lisa     2                                                                                                                              
Felix    Laura     6 Baltic Drive                                                                                                       
Karen    12                                                                                                                             
Thomas   Felicia   424 Suffolk place                                                                                                    
Edward   Bella     666 Vampire Lane                                                                                                     
Jeff     9                                                                                                                              
Jared    7                                                                                                                              
Tim      11                                                                                                                             
;                                                                                                                                       
run;

Re: Conditional Input from Raw Data

jfaruqui — Fri, 16 Nov 2018 11:38:08 GMT

can you please elaborate on what this line does ??
"" do _N_=1 by 1 until(0); ""
This seems to be the kicker in the solution but i cant understand the logic

Re: Conditional Input from Raw Data

s_lassen — Fri, 16 Nov 2018 11:55:57 GMT

Sorry, I overdid that a bit. "do _N_=1 by 1;" is enough. It is just that I started out with "do until(0);" (meaning "do forever", or at least until you reach a LEAVE or DELETE statement), but then I found I that I needed the loop counter (_N_) to identify couples with no children. And I forgot to remove the "until(0)" bit.

"do _N_=1 by 1;" means the same, but with a counter, like "do _N_=1 to infinity;".

If your infile is a real file, and not a Cards statement, the DO UNTIL construct can be used like this (CARDS statement does not accept the END= option):

data want;                                                                                                                              
  infile <filename here!> truncover end=done;                                                                                                    
  input @1 father $9. mother $10. address $40.;                                                                                         
  do _N_=1 by 1 until(done);                                                                                                            
    input child $9. age ?? 3.0 @@;                                                                                                      
    if missing(age) then leave;                                                                                                         
    output;                                                                                                                             
    input;                                                                                                                              
    end;                                                                                                                                
  if _N_=1 then do;  /* output when no children read */                                                                                 
    call missing(Child);                                                                                                                
    output;                                                                                                                             
    end;                                                                                                                                
run;

Which will make the program a little bit simpler.

Re: Conditional Input from Raw Data

jfaruqui — Fri, 16 Nov 2018 12:04:08 GMT

Got it ...
thank you very much !!!
you da man !!!

Re: Conditional Input from Raw Data

Ksharp — Fri, 16 Nov 2018 13:09:55 GMT

data want;                                                                                                                              
input;
length Father   Mother    Address $ 100;
retain Father   Mother    Address ;
temp=scan(_infile_,2,' ');
if not missing(temp) and notdigit(strip(temp)) then do;
Father=scan(_infile_,1,' ');   
Mother=scan(_infile_,2,' ');
call scan(_infile_,2,p,l,' ');
Address=left(substr(_infile_,p+l));
end; 
else do;
Child=scan(_infile_,1,' ');  
Age=scan(_infile_,2,' '); 
end;
drop temp p l;
cards;                                                                                                                                  
Harry    Mary      123 Vermont Ave.                                                                                                     
Joe      3                                                                                                                              
Lisa     2                                                                                                                              
Felix    Laura     6 Baltic Drive                                                                                                       
Karen    12                                                                                                                             
Thomas   Felicia   424 Suffolk place                                                                                                    
Edward   Bella     666 Vampire Lane                                                                                                     
Jeff     9                                                                                                                              
Jared    7                                                                                                                              
Tim      11                                                                                                                             
;                                                                                                                                       
run;
data final_want;
 set want;
 by Father   Mother    Address notsorted;
 if missing(child) and not (first.Address and last.Address) then delete;
run;

Re: Conditional Input from Raw Data

Shmuel — Sun, 18 Nov 2018 05:55:19 GMT

Though solution is already selected, please find attached alternative code debugged and fixed

with hope you find some interest in it:

data test;
   length father  $10 mother  $10 child $10 tst $3
          address $40; /* addapt length to max expected */
   retain father mother address no_of_childs;
   infile datalines truncover;
   input a_line $80.; 
   
   /* check is there an address */
   tst = scan(a_line,3);
   if tst ne ' ' then do;
      if _N_ > 1 then do; 
	 if no_of_childs = 0 then do;
	    child= ' ';
	    child_age = .;
	    output;
        end;
     end;
     a_line = compbl(a_line);
     father = scan(a_line,1);
     mother = scan(a_line,2);
     add_start = index(a_line, scan(a_line, 3));
     address = substr(a_line, add_start);
     no_of_childs = 0;
     return;
   end;
   child = scan(a_line,1);
   child_age = scan(a_line,2);
   output;
   no_of_childs +1;
    
   drop a_line no_of_childs add_start;
datalines;
Harry    Mary      123 Vermont Ave.
Joe      3
Lisa     2
Felix    Laura     6 Baltic Drive
Karen    12
Thomas   Felicia   424 Suffolk place
Edward   Bella     666 Vampire Lane
Jeff     9
Jared    7
Tim      11
;
run;

Re: Conditional Input from Raw Data

jfaruqui — Thu, 22 Nov 2018 16:38:33 GMT

yessir !! that did it too .. thanks