I get the following output :
I want the output as :
Is there a way this can be done in SAS?
Thanks a lot 🙂
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;
Please paste values as plain text. I am unable to copy paste
Also, try proc transpose
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
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;
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
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;
You rock Novinosrin!!
Can you explain this solution pleaseeeee
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
Thanks
I didnt quite understand it 100percent, but thanks a lot:)
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!
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 🙂
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;
@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??)
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.