BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mahler_ji
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
ballardw
Super User

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
Obsidian | Level 7

,

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
Obsidian | Level 7

Thank you so much !!!

This works like a charm.

John

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

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