Solved
New Contributor
Posts: 4

# How to change a very unique and long horizontal data to vertical.

Hello everyone,

I am working on cleaning up a dataset that seems to be very complicated and I have been unable to convert it into a way that I want it to be. Please see the attached file. Each company (referred to as Firm A, B, C, ... ) has a few variables (Date, Var1, Var2, and Var 3) and each variable has up to 2,000 observations. There is a column of 'blank' between the Var3 column of Firm_i and the date variable column of Firm_(i+1). In a spreadsheet, there are about 800 Firms. That is, each firm has 4 variables and one blank column so there are approximately 4,000 columns in the spreadsheet. I would like to convert this into a dataset that is easy for me to analyze on SAS. (Please see the 'after' example.)

Is there any way I can use SAS to manipulate this kind of dataset? My first thoughts were to use Proc Transpose but it did not work the way I wanted it to be. If anyone could share any ideas regarding the solution to this problem, I would be very grateful.

Thank you very much for your time.

Accepted Solutions
Solution
‎09-23-2017 06:39 PM
Super User
Posts: 7,936

## Re: How to change a very unique and long horizontal data to vertical.

[ Edited ]

Do you have to read the spreadsheet?  Can you save the data to a CSV file instead?

If so then something like this should work, assuming that the structure is as predictable as you showed.

``````data firms (keep=firmno firm)
values(keep=firmno date var1-var3 )
;
infile 'myfile.csv' dsd truncover lrecl=1000000 ;
length firmno 8 firm \$20 date var1-var3 8 dummy \$1 ;
informat date mmddyy. ;
format date yymmdd10. ;
if _n_=1 then do;
do firmno=1 by 1 until (firm=' ');
input firm 4*dummy @;
if firm ne ' ' then output firms ;
end;
input / ;
nfirms=firmno;
retain nfirms;
end;
do firmno = 1 to nfirms ;
input date var-var3 dummy @;
output values;
end;
input;
run;

proc sql ;
create table want as
select * from firms a, values b
where a.firmno = b.firmno
order by b.firmno,b.date
;
quit;

``````

All Replies
Solution
‎09-23-2017 06:39 PM
Super User
Posts: 7,936

## Re: How to change a very unique and long horizontal data to vertical.

[ Edited ]

Do you have to read the spreadsheet?  Can you save the data to a CSV file instead?

If so then something like this should work, assuming that the structure is as predictable as you showed.

``````data firms (keep=firmno firm)
values(keep=firmno date var1-var3 )
;
infile 'myfile.csv' dsd truncover lrecl=1000000 ;
length firmno 8 firm \$20 date var1-var3 8 dummy \$1 ;
informat date mmddyy. ;
format date yymmdd10. ;
if _n_=1 then do;
do firmno=1 by 1 until (firm=' ');
input firm 4*dummy @;
if firm ne ' ' then output firms ;
end;
input / ;
nfirms=firmno;
retain nfirms;
end;
do firmno = 1 to nfirms ;
input date var-var3 dummy @;
output values;
end;
input;
run;

proc sql ;
create table want as
select * from firms a, values b
where a.firmno = b.firmno
order by b.firmno,b.date
;
quit;

``````
New Contributor
Posts: 4

## Re: How to change a very unique and long horizontal data to vertical.

Dear Tom,

It worked perfectly and now the dataset looks exactly what I wanted.

Thank you very much for your help!

Kishiyo.

☑ This topic is solved.