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;
... View more
