Architecting, installing and maintaining your SAS environment

Transpose Arrays into Columns - file attached

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 101
Accepted Solution

Transpose Arrays into Columns - file attached

Hello,

I have a dataset that has long arrays for time-series data and I want to turn them into columns that I can work with easily in SAS.  I have attached the file to this discussion, which shows the outstanding shares for a bunch of different stocks over a ten year period.

I want the data to look like:

Name               Date                    Shares

A                           01/04                    10

A                         02/04                    11

A                         03/04                    11

Thanks,

John

Attachment

Accepted Solutions
Solution
‎06-24-2014 01:47 PM
Super User
Posts: 10,538

Re: Transpose Arrays into Columns - file attached

Do you want your FactSet_Universal_screen variable in the output? If not, this may work for you:

data want (keep = FactSet_Universal_screen name date shares);
set r3x_shrout (firstobs=5);
Name=Var2;
array sharevars var3-var122;

do _i_ =1 to dim(sharevars);
  /* this is a very data dependent way of getting the date using an offset*/
 
  date = intnx('month','01JUN2014'd,(-1*_i_));
  shares=input(ShareVars[_i_],?? best10.);
  output;
end;
format date mmyys5.;
run;

proc sort data=want; by name date;run;

View solution in original post


All Replies
Solution
‎06-24-2014 01:47 PM
Super User
Posts: 10,538

Re: Transpose Arrays into Columns - file attached

Do you want your FactSet_Universal_screen variable in the output? If not, this may work for you:

data want (keep = FactSet_Universal_screen name date shares);
set r3x_shrout (firstobs=5);
Name=Var2;
array sharevars var3-var122;

do _i_ =1 to dim(sharevars);
  /* this is a very data dependent way of getting the date using an offset*/
 
  date = intnx('month','01JUN2014'd,(-1*_i_));
  shares=input(ShareVars[_i_],?? best10.);
  output;
end;
format date mmyys5.;
run;

proc sort data=want; by name date;run;

Frequent Contributor
Posts: 101

Re: Transpose Arrays into Columns - file attached

,

Yes, those are the tickers and I need those for merging purposes.

It would be great to see your code so that I can learn from this and I won't have to bother you all again when I have to do this next time.

Thanks,

John

Frequent Contributor
Posts: 101

Re: Transpose Arrays into Columns - file attached

Thank you so much !!!

This works like a charm.

John

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 2314 views
  • 0 likes
  • 2 in conversation