BookmarkSubscribeRSS Feed
Agent1592
Pyrite | Level 9

 

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!

3 REPLIES 3
Satish_Parida
Lapis Lazuli | Level 10

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;
Satish_Parida
Lapis Lazuli | Level 10

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;
Kurt_Bremser
Super User

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 488 views
  • 3 likes
  • 3 in conversation