topic Re: Changing Data Output from Columns to Rows in New SAS User

Changing Data Output from Columns to Rows

new_sas_user_4 — Mon, 15 Oct 2018 17:50:06 GMT

I get the following output :

I want the output as :

Is there a way this can be done in SAS?

Thanks a lot 🙂

Re: Changing Data Output from Columns to Rows

novinosrin — Mon, 15 Oct 2018 17:52:06 GMT

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

Also, try proc transpose

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Mon, 15 Oct 2018 18:01:48 GMT

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

Re: Changing Data Output from Columns to Rows

novinosrin — Mon, 15 Oct 2018 18:06:07 GMT


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;

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Mon, 15 Oct 2018 18:13:52 GMT

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

Re: Changing Data Output from Columns to Rows

novinosrin — Mon, 15 Oct 2018 18:21:43 GMT

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;

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Mon, 15 Oct 2018 18:32:34 GMT

You rock Novinosrin!!

Can you explain this solution pleaseeeee

Re: Changing Data Output from Columns to Rows

novinosrin — Mon, 15 Oct 2018 18:36:38 GMT

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

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Mon, 15 Oct 2018 18:55:53 GMT

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

Re: Changing Data Output from Columns to Rows

novinosrin — Mon, 15 Oct 2018 18:58:30 GMT

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!

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Mon, 15 Oct 2018 19:14:41 GMT

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 🙂

Re: Changing Data Output from Columns to Rows

Tom — Mon, 15 Oct 2018 19:44:17 GMT

Why do you need to change the order of columns?

The normal SAS "trick" to changing the order of the columns is to use a RETAIN statement, since it does not imply a type.

data want;
   retain _name_ area col1 ;
   set have;
run;

Or you could use PROC SQL.

proc sql;
  create table want as 
  select 
 _name_
, area
, col1
  from have
  ;
quit;

Re: Changing Data Output from Columns to Rows

novinosrin — Tue, 16 Oct 2018 15:37:13 GMT

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;

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Tue, 16 Oct 2018 17:31:47 GMT

@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 :

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

Re: Changing Data Output from Columns to Rows

novinosrin — Tue, 16 Oct 2018 17:36:46 GMT

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;

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Tue, 16 Oct 2018 18:30:45 GMT

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

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

Re: Changing Data Output from Columns to Rows

novinosrin — Tue, 16 Oct 2018 18:21:16 GMT

Nothing is too much. I love these questions Gonna go out for lunch to give farewell to my classmate who just graduated. Will respond after lunch(Chicago time).Hang in there. Cheers

Re: Changing Data Output from Columns to Rows

novinosrin — Tue, 16 Oct 2018 22:16:41 GMT

@new_sas_user_4

The set statement reads one record into a memory area known as program data vector that holds the observation while datastep is executed
So when set have executes for the 1st time
A 10 100 1
is read into memory and is held for further processing i.e using other statements that
follows set statement
Now let's get into the loop.
The index variable n when 1,
references the 1st element of the array as j(n)<=>income
Inc_rain_snow=vname(j(n));
this resolves to Inc_rain_snow=vname(j(1))
and that is Inc_rain_snow=vname(income);
similarly
value=j(n);
resolves to value=j(1); and that is value=income;
the rest of the variable values remain undisturbed and the output statment writes the
contents in the memory to the output dataset. The same happens for 3 iterations as we loop through
the 3 elements in the array. So the output statement executes thrice within the loop writing the
value of area A thrice.

Once SAS has executed all the statements for the 1st records, the process repeats for the next record
until end of file as it goes back to the top of the datastep.

Re: Changing Data Output from Columns to Rows

new_sas_user_4 — Wed, 17 Oct 2018 17:14:36 GMT

Thank you so much @novinosrin for taking the time to explain this 🙂

I got it 🙂