DO Loops & Arrays Or Proc Transpose

[art297]

Thomas, You only posted the data for 4 stations, but have indicated that you have seven.

Each station has different questions, different numbers of questions, and the order of question groups appears to be different for the four stations' data that you posted.

Thus, what I was suggesting about using the same program for all seven, obviously wouldn't work.

I would start by writing the code, as I had suggested, for station 1, renaming the variables as desired and including labels for all of the variables.

Then, once you have that working, creating separate code for the other stations may only be a matter of creating separate (but similar) versions for the other stations.  The only things that would change or the variable lables, the number of variables and the order they are input.

If you create the seven files, that way, then you would be able to set them all together in one datastep, only keeping the variables that your boss currently wants included in the file.

[ThomasGeorge]

Hey Art,

Boss told me to just focus on the following questions, sincethese questions are the same with different evaluations.

Canmeds ,Q.1.Patients needs and feelings

Standardized Pt., Q.2.Response to feelings

Canmeds, Q.2. Coherence

Standardized, Pt. Q.3.Degree of Coherence

Canmeds, Q.3.Verbal Expression

Standardized Pt., Q.4. Verbal Expression

Canmeds, Q.4.Non-Verbal Expression

Standardized Pt., Q.5. Non-Verbal Expression

Global Rating, Canmeds-Global rating

Standardized Pt., Q.1.Overall

Competency, Canmeds-Competency

Standardized Pt., Q.6.KeyPoints

She wants both the response and ID variables. All 7 stations have the same pair of questions. Hopefully this helps Art. If not, ask me more.

Thanks,

Thomas

[art297]

Thomas, I'll see if I have time later to take a quick look, but you wouldn't be disagreeing with your boss AT ALL if you read in all of the data.  In fact, the way the spreadsheets are formatted, you don't really have a choice.

Pulling in all of the data up front, but isolating the data she wants, appears like the easiest and most well rounded approach.

[ThomasGeorge]

Art,

I figured it is best to import all the data from all the sheets which is what i have done. She wants only the 6 questions for now, where the number are due for some proposal submission for tomorrow. But i know that this is not the end of it. I have to conceputalize the best way to categorize and clean the data, so in the future I can just pull the variables she needs to do the analysis with the procs she wants. This is my new learning curve categorizing and cleaning data. Appreciate all your help by the way Art. Thomas.

[ThomasGeorge]

Art,

I did all 7 stations maually. Used the replace function in notepad for each station and put it back into SAS. From the experience, i learnt that many of the wordings are different, thus i had to correct many questions way of asking. Nonetheless...i was wondering if there is a search and replace function in SAS.

Thanks,

Thomas

[art297]

Thomas,

Yes there is, codewise and in the editor, but again I'm not sure what you have done.

Below, I've provided some code that addresses the first two stations.  You really have to handle the rest by yourself as I have already put far, far too much time into this, with your not being a paying client.

Before I provide that, though, I do want to point something out.  Most of the people who responded to this forum are doing so out of trying to help others and teach them how to use SAS.  The only thing they ask, in return, is you acknowledge their assistance by indicating when they have provided helpful and/or correct advice.

I don't recall your having done that once!  For me, personally, it really doesn't matter, as I definitely have more than enough thank you points.  However, the points are a way of saying thank you and that is the least one can do given the help that everyone provides every day.

Now, all of that said, check your spreadsheets carefully.  Record 186 for station 2 is out of order and it totally destorys the way I am importing and analyzing the data.  I would show the discrepancy to your boss and ask her if she wants you to MOVE that record IN EXCEL to its correct position.

Here is the code I wrote for dealing with station 1 and station 2 after having moved record 186 to its correct position:

PROC IMPORT OUT= WORK.have1

  DATAFILE= "C:\art\stations.xls"

  DBMS=excel REPLACE;

  GETNAMES=YES;

  sheet="Station 1";

run;

data forlabels;

  set have1 (obs=22 keep=Question_);

  select (_n_);

    when (1-9) question_=cats('Rotation: ',question_);

    when (10-14) question_=cats('Canmed: ',question_);

    when (15) question_="Canmed: Global";

    when (16) question_="Canmed: Competency";

    when (17-22) question_=cats('Standardized: ',question_);

    otherwise;

  end;

run;

proc sql noprint;

  select question_

    into :labels separated by '|'

      from forlabels

  ;

quit;

data want1 (keep=rot: can: stan: prg id: comment);

  set have1;

  array rotation(9);

  array canmed(7);

  array standardized(6);

  retain rotation: canmed: standardized: comment;

  if _n_ eq 1 then do;

    label rotation1=    %scan(&labels.,1 ,"|");

    label rotation2=    %scan(&labels.,2 ,"|");

    label rotation3=    %scan(&labels.,3 ,"|");

    label rotation4=    %scan(&labels.,4 ,"|");

    label rotation5=    %scan(&labels.,5 ,"|");

    label rotation6=    %scan(&labels.,6 ,"|");

    label rotation7=    %scan(&labels.,7 ,"|");

    label rotation8=    %scan(&labels.,8 ,"|");

    label rotation9=    %scan(&labels.,9 ,"|");

    label canmed1=      %scan(&labels.,10,"|");

    label canmed2=      %scan(&labels.,11,"|");

    label canmed3=      %scan(&labels.,12,"|");

    label canmed4=      %scan(&labels.,13,"|");

    label canmed5=      %scan(&labels.,14,"|");

    label canmed6=      %scan(&labels.,15,"|");

    label canmed7=      %scan(&labels.,16,"|");

    label standardized1=%scan(&labels.,17,"|");

    label standardized2=%scan(&labels.,18,"|");

    label standardized3=%scan(&labels.,19,"|");

    label standardized4=%scan(&labels.,20,"|");

    label standardized5=%scan(&labels.,21,"|");

    label standardized6=%scan(&labels.,22,"|");

  end;

  select (mod(_n_,22));

    when (0) do;

               standardized(6)=response_;

               output;

             end;

    when (1) do;

               call missing(of rotation(*));

               call missing(of canmed(*));

               call missing(of standardized(*));

               rotation(1)=response_;

             end;

    when (2,3,4,5,6,7,8,9) rotation(mod(_n_,22))=response_;

    when (10,11,12,13,14,15,16) do;

               canmed(mod(_n_,22)-9)=response_;

               if mod(_n_,22) eq 16 then comment=Comments;

             end;

    otherwise standardized(mod(_n_,22)-16)=response_;

  end;

run;

PROC IMPORT OUT= WORK.have2

  DATAFILE= "C:\art\stations.xls"

  DBMS=excel REPLACE;

  GETNAMES=YES;

  sheet="Station 2";

run;

data forlabels;

  set have2 (obs=23 keep=Question_);

  select (_n_);

    when (1-8) question_=cats('Canmed: ',question_);

    when (9) question_="Canmed: Competency";

    when (10) question_="Canmed: Global";

    when (11-16) question_=cats('Standardized: ',question_);

    when (17-23) question_=cats('Station Specific: ',question_);

    otherwise;

  end;

run;

proc sql noprint;

  select question_

    into :labels separated by '|'

      from forlabels

  ;

quit;

data want2 (keep=can: stan: stat: prg id: comment);

  set have2;

  array canmed(10);

  array standardized(6);

  array station_specific(7);

  retain rotation: canmed: standardized: comment;

  if _n_ eq 1 then do;

    label canmed1=           %scan(&labels.,1, "|");

    label canmed2=           %scan(&labels.,2, "|");

    label canmed3=           %scan(&labels.,3, "|");

    label canmed4=           %scan(&labels.,4, "|");

    label canmed5=           %scan(&labels.,5, "|");

    label canmed6=           %scan(&labels.,6, "|");

    label canmed7=           %scan(&labels.,7, "|");

    label canmed8=           %scan(&labels.,8, "|");

    label canmed9=           %scan(&labels.,9, "|");

    label canmed10=          %scan(&labels.,10,"|");

    label standardized1=     %scan(&labels.,11,"|");

    label standardized2=     %scan(&labels.,12,"|");

    label standardized3=     %scan(&labels.,13,"|");

    label standardized4=     %scan(&labels.,14,"|");

    label standardized5=     %scan(&labels.,15,"|");

    label standardized6=     %scan(&labels.,16,"|");

    label station_specific1= %scan(&labels.,17,"|");

    label station_specific2= %scan(&labels.,18,"|");

    label station_specific3= %scan(&labels.,19,"|");

    label station_specific4= %scan(&labels.,20,"|");

    label station_specific5= %scan(&labels.,21,"|");

    label station_specific6= %scan(&labels.,22,"|");

    label station_specific7= %scan(&labels.,23,"|");

  end;

  select (mod(_n_,23));

    when (0) do;

               station_specific(7)=response;

               output;

             end;

    when (1) do;

               call missing(of canmed(*));

               call missing(of standardized(*));

               call missing(of station_specific(*));

               canmed(1)=response;

             end;

    when (2,3,4,5,6,7,8,9,10) do;

               canmed(mod(_n_,23))=response;

               if mod(_n_,23) eq 9 then comment=strip(Comments);

             end;

    when (11,12,13,14,15,16) do;

               standardized(mod(_n_,23)-10)=response;

             end;

    otherwise station_specific(mod(_n_,23)-16)=response;

  end;

run;