topic Re: How to create a string of values with a condition in SAS Programming

How to create a string of values with a condition

sas_student1 — Thu, 11 Jul 2019 03:34:15 GMT

Hello,

Have a dataset that has an ID column, an item column and column called gap that is created to show the gap in days between the item columns per ID.

I have code where I want to create a string (like a DNA string) of the item by each ID. So for each ID I would create a column called string that would concatenate all the item column values into a long string.

For example for ID 1 (in the data have below) the final string would yield O-O-O-O-O-T and for ID 2 the final string would be O-O-O-O-O-T. I know how to create the above string (see my code below).

What I want to do is bring in the condition that if the gap is greater than 10 then to start the string building anew.

so my output would actually look like this:

Any ideas on what to add to my code below?

What I would want is:

ID string

1 O-O-O

1 O-O-T

2 O-O-O

2 O-O-T

Here is a sample of creating the have dataset.

data have; 
input ID 1. item $1. gap 5.; 
datalines; 
1 O 3 
1 O 4 
1 O 5 
1 O 15 
1 O 3 
1 T 4 
2 O 3 
2 O 5 
2 O 7 
2 O 11 
2 O 3 
2 T O 
; run;

In this code I can create the string by ID, but how do I add the condition that if the gap is greater than 10 to start building a new string?

data want; 
set have; 
by ID; 
retain O T string;
length O T $1. string $50. ; 
if first.ID then do; 
O='0'; T='0';string=' ';end; 
string=catx('-',string,item); 
if item='O' then O ='1';
else if item='T' then T='1'; 
run;

What I would want is:

ID string

1 O-O-O

1 O-O-T

2 O-O-O

2 O-O-T

Re: How to create a string of values with a condition

ScottBass — Thu, 11 Jul 2019 03:29:20 GMT

Edit your post, pasting the code using the Insert SAS Code icon.

Re: How to create a string of values with a condition

sas_student1 — Thu, 11 Jul 2019 03:34:42 GMT

edited

Re: How to create a string of values with a condition

heffo — Thu, 11 Jul 2019 04:28:18 GMT

Your first data step creates some empty variables, but that is easy fixed. So, for the next step there is a bit of if cases in the end. I assume that you only want to output the last row for each ID or if gap is larger than 10.

data have;
	length ID 8 item $ 1 gap 8;
	input ID item gap ;
	datalines;
1 O 3 
1 O 4 
1 O 5 
1 O 15 
1 O 3 
1 T 4 
2 O 3 
2 O 5 
2 O 7 
2 O 11 
2 O 3 
2 T O 
;;;
run;

data want;
	set have;
	by ID;
	retain O T string;
	length O T $1. string $50.;

	if first.ID then do;
		O='0';
		T='0';
		string=' ';
	end;


	if item='O' then
		O ='1';
	else if item='T' then
		T='1';
	if last.ID then do;
		*If this is the last row with this ID, then add item to the string then output it.;
		string=catx('-',string,item);
		output;
	end;
	else if gap > 10 then do;
		*If the gap is larger than 10, then output it first, then "reinit" string with item.;
		output;
		string=item;
	end;
	else 	string=catx('-',string,item);
	*If neither of those cases, add item to string and then, without output, read the next line. ;

run;

Re: How to create a string of values with a condition

hashman — Thu, 11 Jul 2019 04:28:44 GMT

@sas_student1 :

Looks like a veritable DoW-loop job:

data have ;                            
 input ID item :$1. gap ;              
 cards ;                               
1 O 3                                  
1 O 4                                  
1 O 5                                  
1 O 15                                 
1 O 3                                  
1 T 4                                  
2 O 3                                  
2 O 5                                  
2 O 7                                  
2 O 11                                 
2 O 3                                  
2 T 0                                  
;                                      
run ;                                  
                                       
data want (keep = id string) ;         
  do until (last.id) ;                 
    set have ;                         
    by id ;                            
    length string $ 16 ;               
    if gap > 10 then do ;              
      output ;                         
      string = "" ;                    
    end ;                              
    string = catx ("-", string, item) ;
  end ;                                
  output ;                             
run ;

Kind regards

Paul D.

Re: How to create a string of values with a condition

Ksharp — Thu, 11 Jul 2019 11:50:48 GMT

data have ;                            
 input ID item :$1. gap ;              
 cards ;                               
1 O 3                                  
1 O 4                                  
1 O 5                                  
1 O 15                                 
1 O 3                                  
1 T 4                                  
2 O 3                                  
2 O 5                                  
2 O 7                                  
2 O 11                                 
2 O 3                                  
2 T 0                                  
;                                      
run ; 
data temp;
 set have;
 by id;
 if first.id or gap>10 then group+1;
run;
data want;
length want $ 200;
 do until(last.group);
   set temp;
   by group;
   want=catx('-',want,item);
 end;
 drop gap item group;
run;

Re: How to create a string of values with a condition

sas_student1 — Fri, 26 Jul 2019 02:09:56 GMT

@heffo @hashman @Ksharp Thank you to all!