topic Re: Complex Transposing of Panel Data in SAS Programming

Complex Transposing of Panel Data

Agent1592 — Wed, 05 Feb 2020 06:40:38 GMT

Dear Stata Community:

I have the following data. The data year quarter panel data but all the quarter amounts and dates are in one row. I would like to transpose them. Here is the have data set:

data WORK.HAVE;
  infile datalines dsd truncover;
  input ID:32. Company:$9. Period_End_Date:MMDDYY8. Q4_Amount:32. Q3_Amount:32. Q3_Amount0:32. Q1_Amount:32. Q4_Filing_Date:MMDDYY8. Q3_Filing_Date:MMDDYY8. Q2_Filing_Date:MMDDYY8. Q1_Filing_Date:MMDDYY8. First_Date:MMDDYY8.;
  format Period_End_Date MMDDYY8. Q4_Filing_Date MMDDYY8. Q3_Filing_Date MMDDYY8. Q2_Filing_Date MMDDYY8. Q1_Filing_Date MMDDYY8. First_Date MMDDYY8.;
datalines4;
1360442,MICROSOFT,12/31/14,-13070346,0,0,0,04/15/15,11/19/14,08/19/14,05/19/14,04/15/15
;;;;

Here is the want dataset:

data WORK.WANT;
  infile datalines dsd truncover;
  input ID:32. Company:$9. Period_End_Date:MMDDYY8. Quarter:32. Amount:32. Filing_Date:MMDDYY8. First_Date:MMDDYY8.;
  format Period_End_Date MMDDYY8. Filing_Date MMDDYY8. First_Date MMDDYY8.;
datalines4;
1360442,MICROSOFT,12/31/14,1,0,05/19/14,04/15/15
1360442,MICROSOFT,12/31/14,2,0,08/19/14,04/15/15
1360442,MICROSOFT,12/31/14,3,0,11/19/14,04/15/15
1360442,MICROSOFT,12/31/14,4,-13070346,04/15/15,04/15/15
;;;;

I would appreciate efficient code suggestions. Thanks in advance!

Re: Complex Transposing of Panel Data

Satish_Parida — Wed, 05 Feb 2020 06:57:18 GMT

Please let us know if this worked for you.

data WORK.HAVE;
  infile datalines dsd truncover;
  input ID:32. Company:$9. Period_End_Date:MMDDYY8. Q4_Amount:32. Q3_Amount:32. Q3_Amount0:32. Q1_Amount:32. Q4_Filing_Date:MMDDYY8. Q3_Filing_Date:MMDDYY8. Q2_Filing_Date:MMDDYY8. Q1_Filing_Date:MMDDYY8. First_Date:MMDDYY8.;
  format Period_End_Date MMDDYY8. Q4_Filing_Date MMDDYY8. Q3_Filing_Date MMDDYY8. Q2_Filing_Date MMDDYY8. Q1_Filing_Date MMDDYY8. First_Date MMDDYY8.;
datalines4;
1360442,MICROSOFT,12/31/14,-13070346,0,0,0,04/15/15,11/19/14,08/19/14,05/19/14,04/15/15
;;;;
run;

%macro runit;
data want(keep=ID Company Period_End_Date Quarter Amount Filing_Date First_Date);
format Period_End_Date MMDDYY8. Filing_Date MMDDYY8. First_Date MMDDYY8.;
set have;
%do i=1 %to 4;
	Quarter=&i;
	Amount=Q&i._Amount;
	Filing_Date=Q&i._Filing_Date;
	output;
%end;
run;
%mend runit;
%runit;

Re: Complex Transposing of Panel Data

Kurt_Bremser — Wed, 05 Feb 2020 09:18:15 GMT

As it is a simple data issue, no macro coding necessary, only arrays:

data WORK.HAVE;
  infile datalines dsd truncover;
  input
    ID:32.
    Company:$9.
    Period_End_Date:MMDDYY8.
    Q4_Amount:32.
    Q3_Amount:32.
    Q2_Amount:32.
    Q1_Amount:32.
    Q4_Filing_Date:MMDDYY8.
    Q3_Filing_Date:MMDDYY8.
    Q2_Filing_Date:MMDDYY8.
    Q1_Filing_Date:MMDDYY8.
    First_Date:MMDDYY8.
  ;
  format
    Period_End_Date Q4_Filing_Date Q3_Filing_Date Q2_Filing_Date
    Q1_Filing_Date First_Date e8601da10.
  ;
datalines4;
1360442,MICROSOFT,12/31/14,-13070346,0,0,0,04/15/15,11/19/14,08/19/14,05/19/14,04/15/15
;;;;

data want;
set have;
array amounts{4} q1_amount q2_amount q3_amount q4_amount;
array dates{4} q1_filing_date q2_filing_date q3_filing_date q4_filing_date;
format filing_date e8601da10.;
do quarter = 1 to 4;
  amount = amounts{quarter};
  filing_date = dates{quarter};
  output;
end;
keep id company period_end_date quarter filing_date amount first_date;
run;

Note that I always use proper, standardized date formats with 4-digit years and a ymd order. After the Y2K scare, still using 2-digit years anywhere is simply crazy and constitutes gross negligence.

E8601DA10. is equal to YYMMDDD10.

Re: Complex Transposing of Panel Data

Satish_Parida — Wed, 05 Feb 2020 10:11:49 GMT

Another Approach.

data WORK.HAVE;
  infile datalines dsd truncover;
  input ID:32. Company:$9. Period_End_Date:MMDDYY8. Q4_Amount:32. Q3_Amount:32. Q2_Amount:32. Q1_Amount:32. Q4_Filing_Date:MMDDYY8. Q3_Filing_Date:MMDDYY8. Q2_Filing_Date:MMDDYY8. Q1_Filing_Date:MMDDYY8. First_Date:MMDDYY8.;
  format Period_End_Date MMDDYY8. Q4_Filing_Date MMDDYY8. Q3_Filing_Date MMDDYY8. Q2_Filing_Date MMDDYY8. Q1_Filing_Date MMDDYY8. First_Date MMDDYY8.;
datalines4;
1360442,MICROSOFT,12/31/14,-13070346,0,0,0,04/15/15,11/19/14,08/19/14,05/19/14,04/15/15
;;;;
run;

proc transpose data =have out=temp;
by ID Company Period_End_Date First_Date;
var Q1_Amount Q2_Amount Q3_Amount Q4_Amount Q1_Filing_Date Q2_Filing_Date Q3_Filing_Date Q4_Filing_Date;
run;

data want(rename=(col1=Amount) drop=_name_);
format Filing_Date MMDDYY8.;
set temp(obs=4) ;
set temp(firstobs=5 keep=col1 rename=(col1=Filing_Date));
Qurater=substr(_name_,2,1);
run;