SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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
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;
  

View solution in original post


All Replies
Solution
‎09-23-2017 06:39 PM
Super User
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 354 views
  • 1 like
  • 2 in conversation