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.
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
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*/
@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.?
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
@novinosrin Thanks. The format is exactly the same as required. Thanks a lot.
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 25. 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.