topic Re: Transpose array question in SAS Procedures

Transpose array question

Luffy — Tue, 03 Mar 2015 22:06:24 GMT

Hello:

Could someone please explain what Q: is?

what is ne . ?

thanks

Much appreciated

data transposed;set base;

        array Qtr{3} Q:;

        do i = 1 to 3;Period = cat('Qtr',i);Amount = Qtr{i} ;output;end;

        drop Q1:Q3;

        if Amount ne .;

run;

Re: Transpose array question

mohamed_zaki — Wed, 04 Mar 2015 02:27:48 GMT

Could someone please explain what Q: is?

In general colon ( : ) is used to separate the lower and upper bounds of an array’s dimension.

but in your example it has another purpose which is dealing with unknown numbers of variables after the transposing.

By default, PROC TRANSPOSE names these variables Col1, Col2, Col3...up to the maximum.

So you can process the various Col# variables using an ARRAY statement like array Qtr{3} col:;

but it seam that in the PROC TRANSPOSE he added prefix=Q, so he changed col: to Q:

So it is not general syntax, but specific use.

what is ne  . ?

NE is Mnemonic Equivalent to the symbol ^=

ne . mean if not equal to null, mean if not missing for example.

Re: Transpose array question

Reeza — Wed, 04 Mar 2015 03:28:49 GMT

Q: is a shorthand notation to include all variables that start with Q, in this case Q1, Q2, Q3

Ne => Not Equal

. => missing

Not equal to missing

Re: Transpose array question

Luffy — Wed, 04 Mar 2015 16:31:56 GMT

Thank you

I just started using SAS and was learning about it. I probably will be asking lots of basic questions.

Re: Transpose array question

Luffy — Wed, 04 Mar 2015 16:32:58 GMT

Thank you

I just started using SAS and was learning about it. I probably will be asking lots of basic questions.

Re: Transpose array question

Luffy — Thu, 05 Mar 2015 22:26:15 GMT

Thanks guys

Then what about this..

proc transpose data = base out = transposed

(rename=(Col1=Amount) where=(Amount ne .)) name=Period;

shouldn't col2 be amount?? (In the transposed table Col1 is period and col2 is amount) and what is name = Period in this?

Message was edited by: Abhinav Shori

Re: Transpose array question

data_null__ — Thu, 05 Mar 2015 22:41:43 GMT

It's hard to know what COL1 and COL2 since you didn't post BASE or the rest of the PROC TRANSPOSE statements.

NAME=PERIOD changes the name of the name variable (_NAME_) to PERIOD. There is also a similar option, LABEL=, to change the name of the label variable (_LABEL_)

Re: Transpose array question

Luffy — Thu, 05 Mar 2015 22:44:10 GMT

thanks and this is the table

Re: Transpose array question

art297 — Thu, 05 Mar 2015 22:59:39 GMT

You need to supply some example data, in datastep form, for others to understand what you are doing and asking.

Regardless, I'm responding to your initial post. Q1:Q3 is an error that I'm surprised SAS ignores. I presume the coder meant to use Q1-Q3 to drop Q1, Q2, and Q3. However, as written, it will drop any variable that start with Q1, as well as the variable Q3.

Re: Transpose array question

art297 — Thu, 05 Mar 2015 23:14:32 GMT

Your code produces:

Period Amount Col2

q1	3	32
q2	134	23
q3	12	24

Is the table you showed, labeled transpose, what you were expecting?

Re: Transpose array question

data_null__ — Thu, 05 Mar 2015 23:32:47 GMT

Arthur Tabachneck wrote:

. Q1:Q3 is an error that I'm surprised SAS ignores.

Why do you think that is or should be an error? As you state it drops all variables that start with Q1 and the variable Q3. I don't think SAS considers the fact only one variable Q1 starts with Q1. The only thing that looks odd to us perhaps is that there is no space between : and Q3, but non is needed.

Re: Transpose array question

Luffy — Fri, 06 Mar 2015 16:05:25 GMT

@Arthur Tabachneck

the solution you wrote was what I had expected but the solution I got is shown in table (transposed, above).

Thanks

Re: Transpose array question

data_null__ — Fri, 06 Mar 2015 16:13:41 GMT

You must also have BY CUST;.

You need to show your code, always show the code.

Re: Transpose array question

art297 — Fri, 06 Mar 2015 16:16:43 GMT

Post the full proc transpose step that you ran. Given the output you got, I'd presume that it includes a BY statement.

Re: Transpose array question

art297 — Fri, 06 Mar 2015 16:35:09 GMT

DN: I stand corrected! Q1:Q3 is definitely not an error. However, given that the same syntax would represent a range in Excel, I have to wonder how often it has been incorrectly used in SAS code.

Re: Transpose array question

Luffy — Fri, 06 Mar 2015 16:46:23 GMT

proc transpose data = base out = transposed

(rename=(Col1=Amount) where=(Amount ne .)) name=Period;

by cust; run;

this is the complete code

Re: Transpose array question

Luffy — Fri, 06 Mar 2015 16:52:35 GMT

proc transpose data = base out = transposed

(rename=(Col1=Amount) where=(Amount ne .)) name=Period;

by cust; run;

complete code

Re: Transpose array question

art297 — Fri, 06 Mar 2015 17:24:03 GMT

You would get the result I had posted if you delete the statement:

by cust;