I have a table that looks something like this (there are actually way more rows and columns than this):
month a b c
202101 1 2 1
202102 4 3 2
I want to make it look like this:
var 202101 202102
a 1 4
b 2 3
c 1 2
Is your goal by doing this to have a report that can be printed or included in some sort of document?
Or is your goal to create a SAS data set for further analysis? (If so, what analysis?)
What is the purpose of having a data set in this format? What analyses will be done with it after you e-mail it to someone?
Yes, I want to know what analysis the person receiving the data is going to do.
However, my standard advice is this: don't convert/transpose the data to that format. It is a less usable format, requiring more work to program from. It doesn't get any simpler than that!
So the goal is really to create a report from this data! See Maxim 19, Long Beats Wide!
data have;
input month yymmn6. a b c;
cards;
202101 1 2 1
202102 4 3 2
;
data long;
set have;
length variable $ 32;
array vars a--c;
do i=1 to dim(vars);
variable=vname(vars(i));
value=vars(i);
output;
end;
drop a--c i;
run;
proc report data=long;
columns variable month,value;
define variable/group;
define month/across format=yymmn6. order=internal;
define value/sum;
run;
But to back up a step, and apply Maxim 19, you should not be creating this data set in the format given; you should create it initially as a long data set and then all of these gymnastics to get it into a usable form are unnecessary.
@aalluru wrote:
I'm trying to have that as a final dataset but I want it to be on SAS so I can directly email that table to someone
But your requested output is not a dataset. It has MONTH values in the NAMES of the variables instead of a actual data. They will not be able to use that format in any analysis.
Are you trying to make a report to send to someone?
It helps if you have an actual unique key for the orignal dataset. If you don't have one then make one. You can then transpose into a workable format.
data step1;
row+1;
set have;
run;
proc transpose data=have out=tall name=var;
by row month;
var a b c ;
run;
Now you can either make a report.
proc report data=tall;
column var col1,month ;
define var / group 'VAR';
define month / across ' ';
define col1 / sum ' ';
run;
Or you can sort and transpose again. But in that case you will not get "202101" as the column headers unless you have accidentally turned on VALIDVARNAME=ANY.
proc sort data=tall;
by var row;
run;
proc transpose data=tall out=want(drop=_name_);
by var ;
id month;
var col1;
run;
proc print;
run;
Just like SAS/IML did ? Or you want IML code ?
data have;
input month yymmn6. a b c;
cards;
202101 1 2 1
202102 4 3 2
;
proc transpose data=have out=tall name=var;
var a b c ;
id month;
format month yymmn6.;
run;
@aalluru wrote:
I have a table that looks something like this (there are actually way more rows and columns than this):
month a b c
202101 1 2 1
202102 4 3 2
I want to make it look like this:
var 202101 202102
a 1 4
b 2 3
c 1 2
Make your dataset "longer", then use PROC REPORT:
proc transpose data=have out=long;
by month;
var a--c; /* variable list by position, only the first and last variable needs to be named */
run;
proc report data=long;
column _name_ col1,month;
define _name_ / "var" group;
define col1 / "" analysis;
define month / "" across;
run;
Wrap the report step into a proper ODS destination for later use as an email attachment.
Code is untested, posted from my tablet.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.