topic DO Loops & Arrays Or Proc Transpose in SAS Procedures

DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Fri, 02 Mar 2012 21:10:04 GMT

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

DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Fri, 02 Mar 2012 21:34:10 GMT

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

Thanks,

Thomas

DO Loops & Arrays Or Proc Transpose

PGStats — Fri, 02 Mar 2012 21:46:02 GMT

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.

DO Loops & Arrays Or Proc Transpose

art297 — Sat, 03 Mar 2012 01:19:24 GMT

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.

Re: DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Sat, 03 Mar 2012 01:28:27 GMT

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

Re: DO Loops & Arrays Or Proc Transpose

art297 — Sat, 03 Mar 2012 01:35:13 GMT

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.

Re: DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Sat, 03 Mar 2012 16:18:09 GMT

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

Re: DO Loops & Arrays Or Proc Transpose

art297 — Sat, 03 Mar 2012 16:43:05 GMT

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.

DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Sat, 03 Mar 2012 19:49:22 GMT

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.

DO Loops & Arrays Or Proc Transpose

art297 — Sat, 03 Mar 2012 19:59:17 GMT

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.

DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Sat, 03 Mar 2012 21:01:56 GMT

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

DO Loops & Arrays Or Proc Transpose

art297 — Sat, 03 Mar 2012 22:04:18 GMT

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;

DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Tue, 06 Mar 2012 16:28:09 GMT

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

DO Loops & Arrays Or Proc Transpose

art297 — Tue, 06 Mar 2012 16:43:46 GMT

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.

Re: DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Tue, 06 Mar 2012 16:54:55 GMT

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

Re: DO Loops & Arrays Or Proc Transpose

art297 — Tue, 06 Mar 2012 17:22:17 GMT

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.

Re: DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Tue, 06 Mar 2012 17:54:39 GMT

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

Re: DO Loops & Arrays Or Proc Transpose

art297 — Tue, 06 Mar 2012 18:11:29 GMT

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.

Re: DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Tue, 06 Mar 2012 18:19:55 GMT

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.

Re: DO Loops & Arrays Or Proc Transpose

ThomasGeorge — Tue, 06 Mar 2012 20:18:40 GMT

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