DATA Step, Macro, Functions and more

Transposing Data and Date Formatting

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

Transposing Data and Date Formatting

[ Edited ]

Hi

 

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.

 


Accepted Solutions
Solution
‎12-18-2017 01:04 AM
PROC Star
Posts: 1,296

Re: Transposing Data and Date Formatting

[ Edited ]

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

View solution in original post


All Replies
PROC Star
Posts: 1,296

Re: Transposing Data and Date Formatting

[ Edited ]

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*/

 

 

Contributor
Posts: 36

Re: Transposing Data and Date Formatting

Posted in reply to novinosrin

@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.?

Solution
‎12-18-2017 01:04 AM
PROC Star
Posts: 1,296

Re: Transposing Data and Date Formatting

[ Edited ]

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

Contributor
Posts: 36

Re: Transposing Data and Date Formatting

Posted in reply to novinosrin

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 170 views
  • 0 likes
  • 2 in conversation