topic Re: Transpose Arrays into Columns - file attached in Administration and Deployment

Transpose Arrays into Columns - file attached

mahler_ji — Tue, 24 Jun 2014 17:17:42 GMT

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

Re: Transpose Arrays into Columns - file attached

ballardw — Tue, 24 Jun 2014 17:47:46 GMT

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;

Re: Transpose Arrays into Columns - file attached

mahler_ji — Tue, 24 Jun 2014 17:52:15 GMT

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

Re: Transpose Arrays into Columns - file attached

mahler_ji — Tue, 24 Jun 2014 19:19:49 GMT

Thank you so much !!!

This works like a charm.

John