topic Do Loop/Arrays Help in SAS Procedures

Do Loop/Arrays Help

ThomasGeorge — Wed, 29 Feb 2012 15:53:30 GMT

Hopefully, this should be the last change to the requirement i have asked on this post. I would like to transpose the data. I tried proc transpose by ID and SP_MD. But the proc transpose did not work for me. I believe DO Loops and arrays are the best bet, but it has not worked well for me. I worked on the last Do loop and tried modifying what art had posted, but i keep getting error messages. I would like do have the data set set up just like i did on the model sheet the first sheet in the Excel file.

Thanks,

Thomas

Do Loop/Arrays Help

Reeza — Wed, 29 Feb 2012 15:59:29 GMT

Use proc tranpsose instead of an array.

See a good example w/ code here

http://www.ats.ucla.edu/stat/sas/modules/ltow_transpose.htm

Do Loop/Arrays Help

Astounding — Wed, 29 Feb 2012 16:02:10 GMT

Ahh, much better now that you fixed the output structure. This utilizes a neat feature that Art used last week:

data before;

set before;

_name_ = compress( 'st' || put(response,1.) || question, ' .');

run;

proc transpose data=before out=after;

by idnum;

var response;

run;

If you have responses that could be 2 digits long, replace the 1. format with the z2. format.

Good luck.

Do Loop/Arrays Help

art297 — Wed, 29 Feb 2012 16:06:45 GMT

or, yet another way of doing it:

data have;

input IDNUM $ Question $ Response;

cards;

001 Q.1. 1

001 Q.2. 3

001 Q.5. 4

002 Q.1. 0

002 Q.3. 3

002 Q.4. 2

003 Q.5. 3

003 Q.1. 0

003 Q.2. 5

;

data temp;

set have;

question=compress(question,'.');

run;

proc transpose data=temp out=want (drop=_:) prefix=st1;

by idnum;

var response;

id question;

idlabel question;

run;

Do Loop/Arrays Help

ThomasGeorge — Wed, 29 Feb 2012 16:07:44 GMT

astounding. I appreciate your help. I missed out a critical step. Some of the IDNUM have 3-4 Q.1. How would i average the response in the code?

Do Loop/Arrays Help

Tom — Wed, 29 Feb 2012 16:16:04 GMT

It all really depends on what you want to do with the data.

But you can use PROC SUMMARY to create an average of each question.

proc summary data=have nway missing ;

class idnum question ;

var response;

output out=temp mean= ;

run;

Do Loop/Arrays Help

Astounding — Wed, 29 Feb 2012 16:18:24 GMT

Art,

Yes, depending on what is really needed as output I probably should have used:

_name_ = 'st1' || compress(question, ' .');

It's not 100% clear to me whether the new variable names should start with "st1" or whether the 3rd character should vary depending on the response. Given the follow-up information, it should probably remain "st1".

Re: Do Loop/Arrays Help

Astounding — Wed, 29 Feb 2012 16:20:48 GMT

If you combine our solutions, it should work.

First, apply Tom's summary.

Then use Art's code (or my revised code with the updated assignment for _name_), using the output data set from PROC SUMMARY as the input to PROC TRANSPOSE.

Here's a more complete version:

proc summary data=before nway missing;

class idnum question;

var response;

where question ne 'CanM';

output out=temp (keep=idnum question response) mean=;

run;

data temp;

set temp;

_name_ = 'st1' || compress(question, ' .');

run;

proc transpose data=before out=after;

by idnum;

var response;

run;

Do Loop/Arrays Help

art297 — Wed, 29 Feb 2012 16:32:46 GMT

I don't understand why you got duplicate ids, but Astounding's approach should avoid that. How about if you try:

data have;

input IDNUM $ Question $ Response;

cards;

001 Q.1. 1

001 Q.1. 2

001 Q.1. 3

001 Q.2. 3

001 Q.5. 4

002 Q.1. 0

002 Q.3. 3

002 Q.4. 2

002 CanM 4

003 Q.5. 3

003 Q.1. 0

003 Q.2. 5

;

proc summary data=have nway;

class idnum question;

var response;

output out=averages (drop=_:) mean=;

run;

data averages;

set averages;

_name_ = 'st1' || compress(question, ' .');

run;

proc transpose data=averages out=want (drop=_:);

by idnum;

var response;

run;

data want;

retain idnum st1Q1-st1Q5 st1CanM;

set want;

run;

Do Loop/Arrays Help

Astounding — Wed, 29 Feb 2012 16:36:54 GMT

Art,

Looks like our solutions are converging.

Those duplicate ids must have come from a test that didn't use PROC SUMMARY first.

Do Loop/Arrays Help

ThomasGeorge — Wed, 29 Feb 2012 16:59:59 GMT

astounding i have a question. This program that you and art created only calculates the average, but i need standard deviation too?cos i cannot use the proc means step after this, as it will do an averge over an average.

Do Loop/Arrays Help

Astounding — Wed, 29 Feb 2012 17:07:18 GMT

PROC MEANS can generate standard deviations in the output data set, as well as means.

Part of the answer depends on what you want your report to look like. Should the standard deviations form a second row for each IDNUM, or should they form additional columns on the existing row?

Also note, you will have a lot of missing standard deviations when there is only one response.

Do Loop/Arrays Help

art297 — Wed, 29 Feb 2012 17:29:01 GMT

Thomas,

If you look at the code I sent, last, the creation of _name_ was in a different place:

proc summary data=have nway;

class idnum question;

var response;

output out=averages (drop=_:) mean=;

run;

data averages;

set averages;

_name_ = 'st1' || compress(question, ' .');

run;

proc transpose data=averages out=want (drop=_:);

by idnum;

var response;

run;

data want;

retain idnum st1Q1-st1Q5 st1CanM;

set want;

run;

I'll take a look at your latest need now.

Do Loop/Arrays Help

Astounding — Wed, 29 Feb 2012 17:53:18 GMT

Thomas,

I'm afraid that work beckons, and I won't be able to give you a complete answer here, just a few notes.

First, it should be possible to run all 7 STs at once. You might skip this at first, getting it to work for a single ST as a starting point. But it would certainly be possible to code statements like these at the proper points:

_name_ = 'st' || put(st,1.) || compress(question, ' .');

class st idnum question;

But without attempting that hurdle, first, in the PROC SUMMARY, modify the OUTPUT statement to get both statistics:

output out=temp (keep=idnum question mean std) mean=mean std=std;

Then in the DATA step that is needed anyway, construct the combination variable:

data temp;

set temp;

_name_ = 'st1' || compress(question, ' .');

new_response = put(mean, 3.1) || '+-' || put(std, 3.1);

run;

Then use new_response instead of response within PROC TRANSPOSE.

Good luck.

Do Loop/Arrays Help

art297 — Wed, 29 Feb 2012 18:52:18 GMT

Thomas,

There is probably an easier way to accomplish this, but I think that the result at least is what you are looking for:

data have;

input IDNUM $ Question $ Response;

cards;

001 Q.1. 1

001 Q.1. 2

001 Q.1. 3

001 Q.2. 3

001 Q.2. 4

002 Q.1. 0

002 Q.1. 1

002 Q.3. 3

002 Q.3. 2

002 Q.4. 2

002 Q.4. 4

002 CanM 4

002 CanM 5

003 Q.5. 3

003 Q.5. 1

003 Q.1. 0

003 Q.1. 2

003 Q.2. 5

003 Q.2. 7

;

proc summary data=have nway;

class idnum question;

var response;

output out=averages (drop=_:) mean= std= /autoname;

run;

data averages;

set averages;

_name_ = 'sm1' || compress(question, ' .');

run;

proc transpose data=averages (rename=(response_mean=response))

out=part1 (drop=_:);

by idnum;

var response;

run;

data averages;

set averages;

_name_ = 'sd1' || compress(question, ' .');

run;

proc transpose data=averages

out=part2 (drop=_:);

by idnum;

var response_stddev;

run;

proc sql noprint;

select 'st'||trim(substr(name,3))||'='||

'catt(put('||trim(name)||',4.2),"+",put(sd'||

trim(substr(name,3))||',4.2));',

'if strip(st'||trim(substr(name,3))||

')=".+ ." then call missing('||

'st'||trim(substr(name,3))||');',

'length st'||trim(substr(name,3))||' $10;'

into :calcs separated by ' ',

:miss separated by ' ',

:lengths separated by ' '

from dictionary.columns

where libname="WORK" and

memname="PART1" and

name ne "IDNUM"

;

quit;

data want (drop=sm: sd:);

&lengths.;

set part1;

set part2 (drop=idnum);

&calcs.;

&miss.;

run;

Do Loop/Arrays Help

ThomasGeorge — Wed, 29 Feb 2012 22:01:32 GMT

astounding, Art....I appreciate your guys help.

Now the project has changed quite a bit i think, i am not sure how it effects the code. But i am so glad i am learning 😄

I wrote this code so far:

proc sql;

Create table Station1raw as

Select SUBSTR(ID_No_,6,3) as IDNUM, PRG, Question_ , (SUBSTR(Question_,1,4) || Evaluation_Type) as Questionnaire,

SP_vs_MD, Response_ as Response, Out__Of

from Station1

;

quit;

Data station1sp;

set Station1raw;

where SP_vs_MD=1;

Drop PRG;

run;

proc transpose data=station1sp out=station2sp;

by idnum;

var response;

run;

But when i do the proc transpose, i get IDNUM, _NAME_, _LABEL_, COL1, COL2, COL3, COL4 till COL16. I am not clear why it does that. Even if you rewrite the code, please explain. That would greatly help.

Do Loop/Arrays Help

art297 — Wed, 29 Feb 2012 22:40:59 GMT

Thomas,

You will have to provide an example Station1 dataset. It is different from the original one since it is looking for variables that didn't exist in the original data.

Re: Do Loop/Arrays Help

ThomasGeorge — Wed, 29 Feb 2012 23:05:53 GMT

Art,

I have uploaded the file.

Thank you again,

Thomas

Do Loop/Arrays Help

Astounding — Wed, 29 Feb 2012 23:47:15 GMT

Thomas,

One step you could take is to eliminate the DATA steps. The WHERE statements could be added to PROC TRANSPOSE.

But I'm a little curious about how you intend to put the data sets together at the end. You can't do what SAS calls a "merge" because you'll have the same variable names in both data sets. One set of variables will overwrite the other. So do you want to combine them side by side (but changing the variable names for MD vs. SP), or do you want to keep the same variable names and create twice as many observations (while keeping SP_vs_MD to differentiate)?

Do Loop/Arrays Help

art297 — Thu, 01 Mar 2012 00:07:22 GMT

I'm not sure how you are planning to use the dataset you are trying to create, but still I don't think you have described it completely.

For example, from what you told us, I'd expect to create a dataset that had the following variables and data:

IDNUM Q1_.Rotation SP_MD1 Q2_Med SP_MD_2 Q3_Dry SP_MD3 Q4_Frame SP_MD4 Q5_Std SP_MD5

001 1 1 3 0 4 0

002 0 1 2 1 3 0

003 0 1 5 0 3 0

Is that what you are trying to create?