Hi,
I have the following data (have) and I like to get the (want) data. Can someone please help? Thanks.
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;
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.
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.
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;
Please find the attached data. Thanks.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: