topic Re: Transposing Data and Date Formatting in SAS Programming

Transposing Data and Date Formatting

Saba1 — Mon, 18 Dec 2017 05:13:16 GMT

I have my data for a variable in "csv" file where each column is a Quarterly date from year 1997 to 2016 (e.g. date format is: 03/31/1997 06/30/1997 09/30/1997 12/31/1997), and each row is a company (e.g. CUSIP). I want to transpose this data in format below:

CUSIP Date_Quarter Variable

001 03/31/1997 2.3

001 06/30/1997 5.0

001 09/30/1997 4.78

001 12/31/1997 4.5

002 03/31/1998 6.6

002 06/30/1998 9.0

002 09/30/1998 8.7

002 12/31/1998 5.6 and so on.

I have imported the original data file in SAS, which is making Dates as Column Names and now they look like this: "_03_31_1997". This means SAS is not considering them as Dates. I have applied the following code to get required data set;

proc transpose data=have out=want;
by CUSIP ; run;

This gives me accurate results, but the only issue is with formatting of Dates. The Date column is a Character right now with format and informat as "$11".

Please guide me how to get dates format as mmddyy10.

Thanks.

Re: Transposing Data and Date Formatting

novinosrin — Mon, 18 Dec 2017 05:38:58 GMT

If i understand you, you just need your char date to be read into a sas date value and apply mmddyy10. format?

if yes, and all your date values are char date values in the pattern "_03_31_1997";, you could try the following

data final_want;
set want;
corrected_date=put(input(compress(date,'_'), mmddyy10.),mmddyy10.);
run;

/* Drop your old date column with _03_31_1997 kinda values*/

Re: Transposing Data and Date Formatting

Saba1 — Mon, 18 Dec 2017 05:52:54 GMT

@novinosrin Thanks a lot. By using your code, I can change the appearance of Date, i.e. it looks as 12/31/1997 but it is still "Character" with format and informat as $10. How can I change the format to be mmddyy10.?

Re: Transposing Data and Date Formatting

novinosrin — Mon, 18 Dec 2017 06:01:42 GMT

My apologies. You don't need the put function wrapper, and that's what converts back to char again. Please try this:

data final_want;

set want;

corrected_date1=input(compress(date,'_'), mmddyy10.);
format corrected_date1 mmddyy10.;
run;

Notes: Instead of put function to apply format, we are using the format statement to keep the variable numeric and just the format appearance of mmddyy10. is applied. Sorry again

Re: Transposing Data and Date Formatting

Saba1 — Mon, 18 Dec 2017 06:04:08 GMT

@novinosrin Thanks. The format is exactly the same as required. Thanks a lot.