Changing Data Output from Columns to Rows

[new_sas_user_4]

I get the following output :

I want the output as :

Is there a way this can be done in SAS?  

Thanks a lot 🙂

[novinosrin]

Please paste values as plain text. I am unable to copy paste 

Also, try proc transpose

[new_sas_user_4]

Area Income Rain Snow
A 10 100 1
B 20 200 2
C 30 300 3
D 40 400 4

Income A 10
Income B 20
Income C 30
Income D 40
Rain A 100
Rain B 200
Rain C 300
Rain D 400
Snow A 1
Snow B 2
Snow C 3
Snow D 4

[novinosrin]

data have;
input Area $ Income Rain Snow;
cards;
A 10 100 1
B 20 200 2
C 30 300 3
D 40 400 4
;

proc transpose data=have out=_have;
by area;
var income--snow;
run;

proc sort data=_have out=want;
by _name_ area;
run;

[new_sas_user_4]

Thanks Novinosrin!

The output I get using this is :

BUt I want the output as :

AS in I want Income/Rain/Snow to be the first Column 

[novinosrin · Accepted solution]

data have;
input Area $ Income Rain Snow;
cards;
A 10 100 1
B 20 200 2
C 30 300 3
D 40 400 4
;
data t;
retain Inc_rain_snow area value;
set have;
array j income--snow;
do over j;
Inc_rain_snow=vname(j);
value=j;
output;
end;
keep Inc_rain_snow area value;
run;
proc sort data=t out=want;
by Inc_rain_snow area;
run;

[new_sas_user_4]

You rock Novinosrin!!

Can you explain this solution pleaseeeee

[novinosrin]

I used datastep as opposed to proc transpose to do the same transpose, however datastep gives enormous flexibility by group the vars to transpose into a array and changing the shape from rows t columns or vice versa by doing the look up using a loop through all the elements of the array 

[new_sas_user_4]

Thanks 
I didnt quite understand it 100percent, but thanks a lot:)

[novinosrin]

I will pen down a detailed explanation later after my midterm exam 🙂  in 3 hours that is got nothing to do with sas. lol

I shouldn't be even on sas communities today so plz accept my apology, SAS is so addictive , that I can't help.  Have a nice day!

[new_sas_user_4]

hahah, yes, it is !! Please go ahead and prepare for your mid-term! Good Luck 🙂

When you are back, read below and let me know if it makes sense..this is how I understand it:

data t;      // so here you are creating a new table which is blank at the moment but will hold values as you assign them?//
retain Inc_rain_snow area value;           // new column names ??//

set have;                                                //what does set do??//
array j income--snow;                            //a blank array containing j(a variable) income rain snow(income rain snow from table have) ??
do over j;                                               //no idea what this means//
Inc_rain_snow=vname(j);                     //assigning j which is the first column in table "t" the name Inc_rain_snow ??//
value=j;                                                 //how does this work?? How will the values be picked?? //
output;                                                  //suppose this is part of syntax??//
end;                                                      /suppose this is part of syntax??//
keep Inc_rain_snow area value;
run;
proc sort data=t out=want;
by Inc_rain_snow area;
run;

If there is a source where I can understand this with the help of an example, please let me know 🙂

[novinosrin]

Good morning @new_sas_user_4,

Your understanding is indeed in the right direction. Please find my responses in italics

data t;*t is just the name i assigned for the transposed table;

retain Inc_rain_snow area value;*Yes new variables to create but this "retain" is not required, however since you specified the order the compiler

creates the PDV and the output buffer;

set have;*reads records one by one from source table have;

array j income--snow;*groups then variables income rain and snow under one non-scalar value of the array name j;

do over j;*an implicit loop over the array element income--snow;

Inc_rain_snow=vname(j);*the name variable takes the name of the variable name of each element of the array;

value=j;*the new variable value takes the values of each element of the array;

output;*explicit output writes the values to the output buffer/dataset for each iteration of the loop;

end;*of course ends the loop;

keep Inc_rain_snow area value; *keep only the variables of interest;

run;

/*The sort is just to get the order of the values to meet your requirement

So,sorting the temp table t to get the required table want*/

proc sort data=t out=want;

by Inc_rain_snow area;

run;

[new_sas_user_4]

@novinosrin Thanks a lot!!

I understood it better !!

I will read up about the implicit loop , I still didnt understand that part completely

do over j;

Inc_rain_snow=vname(j);

value=j;

For, Inc_rain_snow=vname(j), SAS would do :

I         

R       

S      

How will it know to put the areas(A,B,C,D) in the next column..(through the retain statement??)

[novinosrin]

HI @new_sas_user_4  How about using explicit array/loop like the below to make things clearer. The implicit loop does exactly the below

data have;
input Area $ Income Rain Snow;
cards;
A 10 100 1
B 20 200 2
C 30 300 3
D 40 400 4
;
data t;
retain Inc_rain_snow area value;
set have;
array j(*) income--snow;
do n=1 to 3;*3 elements in your array i.e income rain and snow;
Inc_rain_snow=vname(j(n));
value=j(n);
output;
end;
keep Inc_rain_snow area value;
run;

p

[new_sas_user_4]

Thank you sooooooo much @novinosrin 🙂

I know this might be asking a bit too much , but can you run 1 iteration for me:

eg:

when n=1

inc_rain_snow would have the name of the elements of the array which are :

Income

Rain

Snow 

how will be column area get its value of 

A

A

A

?

 value (10,100,1)  (which is the first row in have table) will get transposed when j=1