extract text from cell and transpose

Solved
Regular Contributor
Posts: 161

extract text from cell and transpose

Dear All:  I have question about how to transpose the following data:

DoctorPatients
Doctor 1P1, P2, P3, P4, P5, P6, P7, P23, ...., P54
Doctor 2P4, P7, P9, P22
Doctor 3P2

into this:

DoctorPatient
Doctor 1P1
Doctor 1P2
Doctor 1P3
Doctor 1P4
Doctor 1P5
Doctor 1P6
Doctor 1P7
Doctor 1P23
Doctor 1...(omitted here)
Doctor 2P4
Doctor 2P7
Doctor 2P9
Doctor 2P22
Doctor 3P2

My difficulties are:

1.  When use the scan function, I have to specify x amount of new variable for each patients.  But I don't know at this point what is the maximum number of patients a doctor may have.  The Excel's Text to Column function allow splitting strings without specifying variable upfront.  Is there a similar function in SAS?

2.  How to transpose the table from fat to thin when the number of variables vary by observation (the number of patients a doctor has in this case).

Thank you -,

Accepted Solutions
Solution
‎07-25-2013 01:09 PM
Frequent Contributor
Posts: 129

Re: extract text from cell and transpose

Caveman529,

That data helps.

I see at least two options

1,  Use only the single delimiter of ';' semicolon.  in the scan function.  From the example, that character seems to only be used at the end of each patient.

2.  If the returned patient is any paitient except for the first patient on record, you will need to eliminate the leading period '.' You could do something like this:

single_patient = substr(single_patient,2) ;

Here is modification of my code to do that:

data  thin (keep = doctor single_patient) ;
set fat ;

length single patient \$ 4 ;

i_scan = 1 ;
do until (scan(patient,i_scan,';') eq '')   ;
single_patient = scan(patient,i_scan,';') ;
if i_scan gt 1 then single_patient = substr(single_patient,2) ;
i_scan + 1 ;
put _all_ ;
output ;
end ;
run ;

Will something like this work?

All Replies
Frequent Contributor
Posts: 129

Re: extract text from cell and transpose

Data thin (keep = doctor single_patient) ;

set fat ;

length single patient \$ 4 ;

i_scan = 1 ;

do until (scan(patient,i_scan,',')) ne '' ;

single_patient = scan(patient,i_scan,',') ;

output ;

end ;

run ;

Note not tested but I think concept will work.

Regular Contributor
Posts: 161

Re: extract text from cell and transpose

Thank you, Larry.  The code somehow didn't iterate for each doctor.

By the way, I just found out that the patient cell separate each patient using two punctuation:;.

How to put them together into the scan function?  I tried (scan(customers, i_scan, ';.'), but SAS thinks I mean . or ;.

Super Contributor
Posts: 312

Re: extract text from cell and transpose

SCAN(A,I,";.");

Should work.

Regular Contributor
Posts: 161

Re: extract text from cell and transpose

When I try SCAN(A,I,";.") by saying that scan(customers, i_can, ",."), SAS still breaks the string whenever it sees . or ;, instead of both of them at the same time.

Frequent Contributor
Posts: 129

Re: extract text from cell and transpose

Interesting.  That is not the way scan function should behave by default.  You can tell it to work that way

Here is sample data I set up to test and the code.  I am a little confused about the pair of characters which make up your delimiter, but I tried several different combinations.

 doctor patient Doctor 1 P1:;P2:;P3:;P4:;P5:;P6:;P7:;P23:;P54 Doctor 3 P2 Doctor 2 P4:;P7:;P9:;P22

And here is the code:

data  thin (keep = doctor single_patient) ;
set fat ;

length single_patient \$ 4 ;

i_scan = 1 ;
do until (scan(patient,i_scan,':;') eq '')   ;
single_patient = scan(patient,i_scan,':;') ;
i_scan + 1 ;
put _all_ ;
output ;
end ;
run ;

Can you send code and log snippets?  Also some of your data might help find the problem.  Can you send a couple of records with only the patient record?

Frequent Contributor
Posts: 129

Re: extract text from cell and transpose

Have you need tried the change Scott suggested?  That along with fixing the two typos in the untested code should work.

My typos were

1.  I was trying to set the length of the single_patient variable but left a blank instead of the connecting uderscore.  It should be

length single_patient \$ 4 ;

2.  I had a misplaced parenthesis in the do until statement.  I suspect you found that. The entire condition should be wrapped in parens.  It should be

do until (scan(patient,i_scan,':;') eq '')   ;

Regular Contributor
Posts: 161

Re: extract text from cell and transpose

Hi, Larry:

Thanks for your tips.  I ran the modified program with do until... eq as you suggested, but the program runs forever without producing results.

The patient variable is a bit more complex that what I described above.  Basically I hope to break the following:

Joe, Smith. (Identifier:2357) (patient attributes (not recent), patient type, address);.    John, Adams. (Identifier:2331) (patient attributes (not recent), patient type, address );.    Bud, A. Bill. (patient attributes (not recent), patient type, address )

into:

(1) Joe, Smith. (Identifier:2357) patient attributes (not recent), patient type, address);.

(2) John, Adams. (Identifier:2331) (patient attributes (not recent), patient type, address );.

(3) Bud, A. Bill. (patient attributes (not recent), patient type, address )

I put in a few space in the boldfaced text so that the break can be easily seen but the original text has no breaks between ;. and next patient.

Thank you -

Solution
‎07-25-2013 01:09 PM
Frequent Contributor
Posts: 129

Re: extract text from cell and transpose

Caveman529,

That data helps.

I see at least two options

1,  Use only the single delimiter of ';' semicolon.  in the scan function.  From the example, that character seems to only be used at the end of each patient.

2.  If the returned patient is any paitient except for the first patient on record, you will need to eliminate the leading period '.' You could do something like this:

single_patient = substr(single_patient,2) ;

Here is modification of my code to do that:

data  thin (keep = doctor single_patient) ;
set fat ;

length single patient \$ 4 ;

i_scan = 1 ;
do until (scan(patient,i_scan,';') eq '')   ;
single_patient = scan(patient,i_scan,';') ;
if i_scan gt 1 then single_patient = substr(single_patient,2) ;
i_scan + 1 ;
put _all_ ;
output ;
end ;
run ;

Will something like this work?

Regular Contributor
Posts: 161

Re: extract text from cell and transpose

Thank you so much, Larry!  It works like magic.

Super Contributor
Posts: 307

Re: extract text from cell and transpose

This also works . . .

data thin;

set fat;

length patient \$3;

drop patients delim modif;

delim = ";";

modif = "mo";

nwords = countw ( patients, delim, modif );

do count = 1 to nwords;

patient = left ( translate ( scan ( patients, count , delim, modif ), '', ':'));

output;

end;

run;

🔒 This topic is solved and locked.