extract text from cell and transpose

Accepted Solution Solved
Reply
Regular Contributor
Posts: 161
Accepted Solution

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?

View solution in original post


All Replies
Frequent Contributor
Posts: 129

Re: extract text from cell and transpose


How about this, where you do not need to know the number of patients.

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: 297

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.

doctorpatient
Doctor 1P1:;P2:;P3:;P4:;P5:;P6:;P7:;P23:;P54
Doctor 3P2
Doctor 2P4:;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.  Smiley Happy Smiley Happy Smiley Happy

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 564 views
  • 0 likes
  • 4 in conversation