How do i create a time series data?

[bd_user_10]

Hi,

I have the following data (have) and I like to get the (want) data. Can someone please help? Thanks.

[ballardw]

So others can see what you have:

Name,Currency,ISIN,FundId,Jan-07,Feb-07,Mar-07,Apr-07,May-07,Jun-07,Jul-07,Aug-07,Sep-07,Oct-07,Nov-07,Dec-07,Jan-08,Feb-08,Mar-08,Apr-08,May-08,Jun-08,Jul-08,Aug-08,Sep-08,Oct-08,Nov-08,Dec-08,Jan-09,Feb-09,Mar-09,Apr-09,May-09,Jun-09,Jul-09,Aug-09,Sep-09,Oct-09,Nov-09,Dec-09
Aktia Capital,Euro,FI0008804976,FSGBR054CE,"231,833,292.00","229,732,061.00","235,672,556.00","253,074,339.00","264,353,955.00","262,290,664.00","260,087,849.00","247,331,804.00","250,620,615.00","259,145,263.00","236,121,904.00","229,805,352.00","197,558,866.00","207,023,238.00","203,433,115.00","205,369,069.00","212,242,222.00","185,838,666.00","176,881,454.00","178,506,030.00","149,003,554.00","127,065,184.00","114,413,247.00","111,345,494.00","113,346,167.00","108,796,545.00","109,972,269.00","135,340,169.00","147,107,856.00","144,865,502.00","153,812,951.00","169,606,967.00","180,259,258.00","173,166,211.00","178,522,190.00","189,106,349.00"
Aktia Eurooppa,Euro,FI0008804836,FSGBR054CD,"125,210,533.00","124,270,659.00","125,384,991.00","132,537,672.00","138,393,186.00","137,814,277.00","135,261,466.00","129,950,036.00","130,301,746.00","134,980,502.00","127,748,460.00","126,880,714.00","106,083,074.00","107,307,702.00","102,239,659.00","108,017,056.00","111,635,520.00","97,440,322.00","95,001,922.00","94,702,550.00","79,042,252.00","64,408,743.00","60,215,744.00","59,993,019.00","58,293,962.00","53,718,759.00","53,613,225.00","63,066,417.00","68,060,002.00","68,563,303.00","74,154,463.00","79,325,953.00","84,187,061.00","85,859,184.00","87,530,187.00","94,602,486.00"
Aktia Global,Euro,FI0008804851,FSGBR054CF,"60,283,755.00","58,375,345.00","57,917,716.00","58,549,856.00","60,167,774.00","59,201,369.00","56,795,954.00","54,745,974.00","54,816,538.00","53,532,329.00","49,521,251.00","49,071,411.00","42,919,154.00","43,031,875.00","39,943,701.00","42,778,690.00","44,116,924.00","39,922,386.00","39,579,436.00","40,691,250.00","35,636,747.00","32,084,875.00","30,537,359.00","28,413,706.00","28,732,474.00","26,122,005.00","25,228,678.00","29,285,864.00","30,053,093.00","30,529,659.00","32,673,051.00","33,547,538.00","34,389,442.00","34,607,413.00","35,152,079.00","37,313,015.00"

Please post text, such as CSV file examples into a code box opened using the forum's </> icon.

SAS time series data generally has one record per time period with a variable holding the date value which should be a SAS date valued numeric.

Your data source also did not do you a favor by having values with commas in a CSV file so you need to work around those a bit.

This will read your example data:

data read;
   /* replace DATALINES on next statement with the the 
      path and name of your csv file like
      "c:\folder\filename.csv"
   */
   infile datalines dlm=',' dsd;
   informat name $25. currency $10. ISIN $15. fundid $15. value comma18.;
   input name currency isin fundid @;
   do year = 2007 to 2009;
      do month=1 to 12;
         date = mdy(month,1,year);
         input value  @;
         output;
      end;
   end;
   input;
   drop month year;
   format date Monyy7. value comma18.;
      
datalines;
Aktia Capital,Euro,FI0008804976,FSGBR054CE,"231,833,292.00","229,732,061.00","235,672,556.00","253,074,339.00","264,353,955.00","262,290,664.00","260,087,849.00","247,331,804.00","250,620,615.00","259,145,263.00","236,121,904.00","229,805,352.00","197,558,866.00","207,023,238.00","203,433,115.00","205,369,069.00","212,242,222.00","185,838,666.00","176,881,454.00","178,506,030.00","149,003,554.00","127,065,184.00","114,413,247.00","111,345,494.00","113,346,167.00","108,796,545.00","109,972,269.00","135,340,169.00","147,107,856.00","144,865,502.00","153,812,951.00","169,606,967.00","180,259,258.00","173,166,211.00","178,522,190.00","189,106,349.00"
Aktia Eurooppa,Euro,FI0008804836,FSGBR054CD,"125,210,533.00","124,270,659.00","125,384,991.00","132,537,672.00","138,393,186.00","137,814,277.00","135,261,466.00","129,950,036.00","130,301,746.00","134,980,502.00","127,748,460.00","126,880,714.00","106,083,074.00","107,307,702.00","102,239,659.00","108,017,056.00","111,635,520.00","97,440,322.00","95,001,922.00","94,702,550.00","79,042,252.00","64,408,743.00","60,215,744.00","59,993,019.00","58,293,962.00","53,718,759.00","53,613,225.00","63,066,417.00","68,060,002.00","68,563,303.00","74,154,463.00","79,325,953.00","84,187,061.00","85,859,184.00","87,530,187.00","94,602,486.00"
Aktia Global,Euro,FI0008804851,FSGBR054CF,"60,283,755.00","58,375,345.00","57,917,716.00","58,549,856.00","60,167,774.00","59,201,369.00","56,795,954.00","54,745,974.00","54,816,538.00","53,532,329.00","49,521,251.00","49,071,411.00","42,919,154.00","43,031,875.00","39,943,701.00","42,778,690.00","44,116,924.00","39,922,386.00","39,579,436.00","40,691,250.00","35,636,747.00","32,084,875.00","30,537,359.00","28,413,706.00","28,732,474.00","26,122,005.00","25,228,678.00","29,285,864.00","30,053,093.00","30,529,659.00","32,673,051.00","33,547,538.00","34,389,442.00","34,607,413.00","35,152,079.00","37,313,015.00"
;

The @ on the input statements holds the current line on the input buffer for reading, so requires one Input without reading anything to advance to the next line of the data. The Loop for year and month basically will only work with files that have values from Jan to Dec of each year. The explicit OUTPUT in the loop writes one record as each month is read. The date uses the first of the month, which how SAS usually treats date values when read that don't have a specific day. You could use any day you want in the MDY function that occurs in every month if preferred but I suspect 1 should work for most things. If for some reason you would prefer to have the last day of a month use : date= intnxt('month',date,0,'E'); to advance the date to the last day of the month.

If you created those values with commas by exporting from a spreadsheet you might save yourself some headaches by highlighting all of those columns and picking a cell format with commas before exporting to CSV.

[Reeza]

Transposing data tutorials:
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/

In this case, I would recommend a data step approach. Give it a try and if you're still having issues, post your code. 

---

@bd_user_10 wrote:

Hi,

I have the following data (have) and I like to get the (want) data. Can someone please help? Thanks.

---

[ghosh · Accepted solution]

FILENAME mycsv '~/dat/have.csv';
PROC IMPORT DATAFILE=mycsv
	DBMS=CSV replace
	OUT=csv_imp;
	GETNAMES=YES; 	
RUN;

proc transpose data=csv_imp out=long;
by Name Currency ISIN fundid;
var Jan_:   Feb_:   Mar_:   Apr_:   May_:   Jun_:   Jul_:   Aug_:   Sep_:   Oct_:   Nov_:   Dec_:
;
run;
data want;
  length date 8.;
set long;
  length date FundSize 8;
   date=input(compress(cats('01-',_name_),'-'),date9.); 
   FundSize=input(col1,comma12.2);
   format date monyy. FundSize comma14.2;
   drop _name_ col1;
run;

[bd_user_10]

Hi Ghosh, Thanks for your codes! I appreciate that. By the way, how do we change the var in proc transpose if we have daily data instead of monthly data? Could you please clarify that? Thanks.

[ghosh]

You're welcome.

What format is your daily data in?

[bd_user_10]

Please find the attached data. Thanks.

[ghosh]

Hi,

This will need a slightly different approach.  I would suggest save your file in xlsx and follow my code in this link:

https://communities.sas.com/t5/SAS-Programming/Transposing-Excel-data-ON-IMPORT/m-p/676791#M204092

If you still need help please post it as a new thread.

Thanks

[bd_user_10]

Thank you very much. It worked!