Making column into row

[Ramin1]

Hello Experts, 

I want to make one of the column of my data as the top row or header of the data. I am attaching the file here.

I have

GVKEY SIC DATE EXECID YEAR

123 5555 31/12/2001 246 2001

123 5555 30/6/2003 234 2003

I want 

year 2001 2002 2003 ....

execid 

246  1 0 0

234  0 0 1

I want to check Execid with year , execid is executives ID. If an executive present in that year then it will be 1 otherwise zero.

[Kurt_Bremser · Accepted solution]

From your csv file, this creates the report:

data have;
infile '/folders/myfolders/have.csv' firstobs=2 dlm="," truncover;
input
  GVKEY :$6.
  DATE :mmddyy10.
  SIC :$4.
  EXECID :$5.
  year
;
format date yymmdd10.;
run;

options missing=0;

proc report data=have;
column execid n,year;
define execid / group;
define year / "" across;
define n / "";
run;

[Ramin1]

Thanks a lot KurtBremser. It works fine. If I want to keep one more column in the left for instance in this case GVKEY, what I have to do additionally.

Is it OK if I use the following code?

options missing=0;

proc report data=have;
column gvkey execid n,year;
define gvkey / group;
define execid / group;
define year / "" across;
define n / "";
run;

[Kurt_Bremser]

You can simplify the code, as @andreas_lds has shown:

options missing=0;

proc report data=have;
column gvkey execid year;
define gvkey / group;
define execid / group;
define year / "" across;
run;

[Ramin1]

Hi KurtBremser,

When I use the code, I get the following sas file in the output. But the problem is the file header do not have the year, instead something null. I want the year in the header. I get the ODS excel or text file which provide me year as the header but how can I get SAS file like this?

[Kurt_Bremser]

Please post your code.

[Ramin1]

 Thanks a lot for your reply. I really appreciate it. Now, I have the following queries 

1. What I wanted to say, in the sas output (have file in the picture below), I want same file as in report. In the report I have the years in the header but in sas output file there are _C2_   _C3_ ... In excel file it is OK.

#CODE

options missing=0;
ods escapechar = "^";
ods excel file = "C:\RA\Data Analysis\test1.xlsx";
ods text = "New Report";

proc report data=tenure out=have;
column execid n,fyear;
define execid / group;
define fyear / "" across;
define n / "";
run;

ods excel close;

2. Now, I want to keep the value of confidence column in the place of 1/0, how can I do that?
For instance, the output should be something like this
Execid 2000 2001 2002 ...

09294  .0284 .0024 

00006  1.8871 

I am adding a sas file consisting of confidence column.

[Kurt_Bremser]

To get a dataset, use PROC TRANSPOSE:

(I downloaded your dataset to my shared folder)

proc sort
  data=myfold.ten
  out=ten
;
by execid year;
run;

proc transpose
  data=ten
  out=want (drop=_name_)
  prefix=Y_
;
by execid;
id year;
var confidence;
run;

For the report, use confidence instead of the N statistic:

proc report data=myfold.ten;
column execid confidence,year;
define execid / group;
define year / "" across;
define confidence / "";
run;

[Kurt_Bremser]

Note that wide datasets are mostly useless, especially such like you show. Such layouts are only good as reports meant for human consumption.

Wide datasets are only needed for regression analysis; are you trying to do such?

If yes, use PROC TRANSPOSE after introducing a dummy variable:

data have;
infile '/folders/myfolders/have.csv' firstobs=2 dlm="," truncover;
input
  GVKEY :$6.
  DATE :mmddyy10.
  SIC :$4.
  EXECID :$5.
  year
;
format date yymmdd10.;
value = 1;
run;

proc sort data=have;
by execid year;
run;

proc transpose
  data=have
  out=want (drop=_name_)
  prefix=Y_
;
by execid;
id year;
var value;
run;

If you want the report as an Excel sheet (which is often the case), just use ODS EXCEL around the PROC REPORT.

[andreas_lds]

So, you want a report, right?

option missing='0';

proc report data=have;
   columns execId year;
   define execID / order;
   define year / across;
run;

options missing='.';