Transpose Arrays into Columns - file attached

[mahler_ji]

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

[ballardw · Accepted solution]

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;

[mahler_ji]

,

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

[mahler_ji]

Thank you so much !!!

This works like a charm.

John