BookmarkSubscribeRSS Feed
ThomasGeorge
Calcite | Level 5

I have an Excel File and i used art's code from last time. It worked for a different requirement, but the requirement has changed. I have put up a model of how the final data should look like. But it is only for one station. I am looking to do it for 7 stations, but this is the code i used last time. If it can be tweaked, please explain it to me. The final data will have 311 variable and 24 observations.

Libname Thomas "C:\Users\tgeorge\Documents\Rulan_Nephrology";

%Macro Import(Sheet);

          PROC IMPORT OUT=STATION&Sheet

                      DATAFILE= "C:\Users\tgeorge\Documents\Rulan_Nephrology\OSCE_NEPHRO_01_27_2012.xls"

                      DBMS=EXCEL REPLACE;

               RANGE="'Station &Sheet.$'";

               GETNAMES=YES;

               MIXED=YES;

               SCANTEXT=YES;

               USEDATE=YES;

               SCANTIME=YES;

          RUN;

%Mend Import;

%Import(1)

%Import(2)

%Import(3)

%Import(4)

%Import(5)

%Import(6)

%Import(7)

data station1;

set station1;

rename Response_=Response;

run;

data station1final (keep=st1rot: st1can: st1stan: id_:);

  set station1;

  array st1rotation(9);

  array st1canmed(7);

  array st1standardized(6);

  retain st1rotation: st1canmed: st1standardized:;

  select (mod(_n_,22));

    when (0) do;

               st1standardized(6)=response;

               output;

             end;

    when (1) do;

               call missing(of st1rotation(*));

               call missing(of st1canmed(*));

               call missing(of st1standardized(*));

               st1rotation(1)=response;

             end;

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

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

                  st1canmed(mod(_n_,22)-9)=response;

                end;

    otherwise st1standardized(mod(_n_,22)-16)=response;

  end;

run;

Thanks,

Thomas

20 REPLIES 20
ThomasGeorge
Calcite | Level 5

Also, can anyone explain the  MOD fuunction with the code above?

Thanks,

Thomas

PGStats
Opal | Level 21

MOD is the modulo, or remainder, function. For instance, mod( _n_ , 22 ) will return the remainder of the division of _n_ by 22, an integer between 0 and 21.

PG

PG
art297
Opal | Level 21

Thomas,

PGStats already answered your question, but I really think you ought to offer you boss two choices: (1) the file that you are planning/trying to create and (2) creating a much narrower spreadsheet with only the 22 questions identified (along with the other information like id, ec.) and an extra field for station.

Making separate fields for each question/station really doesn't make a lot of sense.

ThomasGeorge
Calcite | Level 5

Art,

I talked to the BOSS. She said she does not want two seperate columns for SP and MD, cos when she wants to do a proc means, she cannot use the SP and MD as class variables. I tried convincing her that we can have 2 columns for SP and MD, so that we can stick with just 22 variables of responses and just refer back to the Excel sheet to see the "out of" score. And to do means, and std, we can just do the data step. Having 311 variables with 24 observations, does not make too much sense for me. But as you said art the boss is always right!.

Thanks,

Thomas

art297
Opal | Level 21

I wasn't suggesting separate columns for sp and md.  Sp and md only appear to identify the questions.  I was just suggesting a separate column for station (i.e., one column that shows the station the data is coming from).

However, like you said, the BOSS makes the ultimate decision and, if rule two conflicts, refer back to rule #1.  Just don't be surprised if you ultimately have to recreate the file the way I had described.

ThomasGeorge
Calcite | Level 5

Art,

I talked to the boss as you asked me too and she said she wants it in the way that is on the Excel sheet. I know for sure she will change her mind, but i have come to understand that is the nature of work. I was thinking from the code you generated, is it better to generate the first data set on where=SP and then another data set on where=MD and then finally append them? Do it for all 7 stations and do a final merge based on ID number and SP/MD variable and it should work.

Thanks,

Thomas

art297
Opal | Level 21

Thomas,  I must have missed something because I don't understand the model you posted a couple of days ago.

Specifically, I don't understand what differentiates SP and MD values in your breakdown, or the second set of values (i.e., rows 17 thru 28) in your model.  Regardless, I think you have already amassed enough tools to be able to put it together yourself.

ThomasGeorge
Calcite | Level 5

Art,

I am studying your code. I am learning to expand it. So i wrote the code this way:

data station1final (keep=st1rot: st1can: st1stan: id_:);

  set station1;

  array st1rotationresp(9);

  array st1rotationout (9);

  array st1canmedresp(7);

  array st1canmedout(7);

  array st1standardizedresp(6);

  array st1standardizedout(6);

  retain st1rotation: st1canmed: st1standardized:;

select (mod(_n_,22));

                    when (0) do;

                              If st1standardizedout(6) then st1standardizedout(6)= out__of; else st1standardizedout(6)='Y:1;N:0';

        output;

                    end;

                    when (0) do;

                              st1standardizedresp(6)=response;

                    output;

        end;

Otherwise;

end;

run;

At the 6th observation of standardized the out of variable has character values. I tried the above statement, but it did not work.

art297
Opal | Level 21

Thomas,

I don't understand what your input data look like at the point you are setting Station1 or why you would declare all of the arrays if you aren't going to use them in your code.

Two things though: (1) an array can only take character or numeric values, not both

(2) a select statements options can only be used once.  Thus, your second use of when(0) would never be executed.

Plus, like I've mentioned, from the code and data snippets you've presented, I DON'T understand what you are trying to do.

ThomasGeorge
Calcite | Level 5

Art,

I am studying your code, so i was trying different things to see why it works one way, but does not the other. Thank you for the info on the select statement and array. Now the mod function, I realise that the MOD function excutes for every nth observation, But can you explain why the call missing function? and it's purpose in this code?

Thanks,

Thomas

art297
Opal | Level 21

Thomas,

My original code was designed to read the 22 records that comprised each subject's data.  Some of the info was only captured on a specific record and some of the information was repeated 22 times.

When the mod function returned a 1, SAS was reading a subject's first record.  As such, all of the previous data was for the previous subject, thus was set to missing values.  Similarly, when reading the first nine records in each set, it was picking up the rotation data, when reading the next seven records was picking up the canmed data (and when reading the last record in that group it was also picking up the comment) and, finally, when reading the last seven records it was picking up the standardized data (and, for the last one where mod would return a 0, outputing the record).

The same method should work for all of your files.  While I don't understand what you are trying to do now, it sounds like you are still trying to read the same data, just assign them to different variables and rename those variables.

PROC IMPORT OUT= WORK.have

  DATAFILE= "C:\art\Thomas.xlsx"

  DBMS=excel REPLACE;

  GETNAMES=YES;

run;

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

  set have;

  array rotation(9);

  array canmed(7);

  array standardized(6);

  retain rotation: canmed: standardized: comment;

  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;

ThomasGeorge
Calcite | Level 5

Art,

I wrote this code. I was wondering if there is a more efficient way of writing this out. I wanted to use First.SP_vs_MD because it changes between 1 and 0. But it did not work out so i wrote it out this way.

data station1 (drop=Response_);

set station1;

Response=Response_;

run;

proc sort data = station1

          out = station1;

          by ID_No_ SP_vs_MD;

          run;

Data station1md (drop = comments PRG Evaluation_Type Question_ SP_vs_MD Response Out__Of ID);

set station1;

By ID_no_ ;

SP_MD = 1;

If question_ = "Q.1. Patients Needs & Feelings" then do;

   Q1Resp = response;

   Q1Out_of = Out__of;

   end;

If question_ = "Q.2. Coherence" then do;

   Q2Resp = response;

   Q2Out_of = Out__of;

   end;

If question_ = "Q.3. Verbal Expression" then do;

   Q3Resp = response;

   Q3Out_of = Out__of;

   end;

If question_ = "Q.4. Non-Verbal Expression" then do;

   Q4Resp = response;

   Q4Out_of = Out__of;

   end;

If question_ = "CanMEDs - Global Rating" then do;

   Q5Resp = response;

   Q5Out_of = Out__of;

   end;

If question_ = "CanMEDs - Competency" then do;

   Q6Resp = response_ ;

   Q6Out_of = Out__of;

   end;

if last.ID_no_ then          output;

retain Q1Resp Q1Out_of Q2Resp Q2Out_of Q3Resp Q3Out_of Q4Resp Q4Out_of Q5Resp Q5Out_of Q6Resp Q6Out_of;

run;

Data station1sp (drop = comments PRG Evaluation_Type Question_ SP_vs_MD Response Out__Of ID);

set station1;

By ID_no_ ;

SP_MD = 0;

If question_ = "Q.2. Response to Feelings" then do;

   Q1Resp = response;

   Q1Out_of = Out__of;

   end;

If question_ = "Q.3. Degree of Coherence" then do;

   Q2Resp = response;

   Q2Out_of = Out__of;

   end;

If question_ = "Q.4. Verbal Expression" then do;

   Q3Resp = response;

   Q3Out_of = Out__of;

   end;

If question_ = "Q.5. Non-Verbal Expression" then do;

   Q4Resp = response;

   Q4Out_of = Out__of;

   end;

If question_ = "Q.1. Overall" then do;

   Q5Resp = response;

   Q5Out_of = Out__of;

   end;

If question_ = "Q.6. Key Points" then do;

   Q6Resp = response;

   Q6Out_of = Out__of;

   end;

if last.ID_no_ then          output;

retain Q1Resp Q1Out_of Q2Resp Q2Out_of Q3Resp Q3Out_of Q4Resp Q4Out_of Q5Resp Q5Out_of Q6Resp Q6Out_of;

run;

data station1modify;

set station1md station1sp;

run;

Also, how can i do this for all 7 stations instead of writing and copying down the codes?

Thanks,

Thomas

art297
Opal | Level 21

Thomas,

You never responded to my last post and I really don't have any idea what the file looks like or where the variables come from that you refer to in your code.  I saw your post on SAS-L, but didn't respond to it for the same reason.

If you are following the method I had suggested, the variables you refer to don't exist.  Rather, the variables can be renamed and labeled during the same run as they are input, thus requiring only ONE program, and the only things that would have to change are the names of the 7 input files and possibly the station numbers.

However, it appears like you went a totally different route and I don't understand your questions.

ThomasGeorge
Calcite | Level 5

Art,

My boss changed her mind. She now wants only 6 specific questions like i indicated in the code which is the same in all seven stations. She apparently changed her mind on how she wants to submit some proposal for grant. I am sorry for not responding to th last post on time.

I will post the file again on this discussion board.

Thanks,

Thomas

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 1350 views
  • 1 like
  • 3 in conversation