topic Re: Making column into row in SAS Programming

Making column into row

Ramin1 — Thu, 06 May 2021 07:33:01 GMT

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.

Re: Making column into row

Kurt_Bremser — Thu, 06 May 2021 07:51:55 GMT

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;

Re: Making column into row

andreas_lds — Thu, 06 May 2021 07:54:38 GMT

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='.';

Re: Making column into row

Ramin1 — Thu, 06 May 2021 08:28:12 GMT

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;

Re: Making column into row

Kurt_Bremser — Thu, 06 May 2021 09:53:09 GMT

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;

Re: Making column into row

Ramin1 — Mon, 17 May 2021 23:55:42 GMT

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?

Re: Making column into row

Kurt_Bremser — Tue, 18 May 2021 05:17:48 GMT

Please post your code.

Re: Making column into row

Kurt_Bremser — Tue, 18 May 2021 14:36:31 GMT

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.

Re: Making column into row

Ramin1 — Wed, 19 May 2021 22:27:26 GMT

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.

Re: Making column into row

Kurt_Bremser — Thu, 20 May 2021 11:40:59 GMT

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;